package com.navi.loaddata.loadService.impl.spcData;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.ControldataTablename;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.oracleEntity.spc.*;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.loadService.impl.shtWipLoad.LoadShtWipTmpService;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.*;

import static java.util.stream.Collectors.groupingBy;


@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})
public class LoadSpcDataService {
    private static final Logger logger = LoggerFactory.getLogger(LoadSpcDataService.class);
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");//yyyy-mm-dd hh:mm:ss   yyyyMMddHHmmss
    public static final String CONTROLDATA_QUERY_COLUMN =" chartid,charttype,factoryname,subfactoryname,controldataseq,dcdataid,db_timestamp_src," +
            "glass_id,eventtime,eventtime_pre,itemname,productspecname,eccode,processoperationname,machinename,unitname,recipename," +
            "procprocessoperationname,procmachinename,procunitname,procrecipename,runmode,ownercode,rtc,qtap,uspec,target,lspec,ucl1," +
            " cl1,lcl1,ucl2,cl2,lcl2,ucl3,cl3,lcl3,d_xbar,d_sdev,d_rng,d_unf1,d_unf2,d_max,d_min,d_x,d_rm,d_np,d_p,d_c,d_u,del_mark," +
            "oos,ooc1,ooc2,ooc3,oor1,oor2,oor3,sitecount,rawdatas";

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    QueryRunner ora_spc = JdbcUtil.getQueryRunner_ora_spc();
    public int loadData(EtlConfD etlConfD) throws Exception {
        int[] count ={0};
        try {
            logger.info("Job {} start running.{}---{}", "LoadSpcDataService" ,etlConfD.getRunStartTimestamp(),etlConfD.getRunEndTimestamp());
           /* List<String> chartIdList =getCharIdListByTime(etlConfD);
            if(chartIdList.size()<=0)return count[0];
            List<ChartType> chartTypeList = getChartTypeByChartId( etlConfD, chartIdList);*/
            List<ChartType> chartTypeList = getChartTypeByChartId( etlConfD);
            if(chartTypeList.size()<=0)return count[0];
            List<String> tableNameList =getControlDataTableNames();
            List<ChartData> chartDataList =new ArrayList<>();
            List<ChartSpec> chartSpecOfItem1List=new ArrayList<>();
            List<ChartSpec> chartSpecOfItem2List=new ArrayList<>();
            List<ChartSpec> chartSpecOfItem3List=new ArrayList<>();
//            List<String> tmpList = new ArrayList<>();
            long startTime=System.currentTimeMillis();
            int numFlg=0;
            for(ChartType chartType:chartTypeList){
                logger.info("{} #################################################：{}",numFlg,new Date());
                numFlg++;
                long startTimeQ=System.currentTimeMillis();
                List<ControlData> controlDataList=new ArrayList<>();
                chartDataList = getChartDataByChartId( etlConfD, chartType.getChartId());
                if(chartDataList.size()<=0) continue;
                if(StringUtils.isNotBlank(chartType.getItem1())){
                    chartSpecOfItem1List = getChartSpecByChartId( etlConfD, chartType.getChartId(), chartType.getItem1());
                }
                if(StringUtils.isNotBlank(chartType.getItem2())){
                    chartSpecOfItem2List = getChartSpecByChartId( etlConfD, chartType.getChartId(), chartType.getItem2());
                }
                if(StringUtils.isNotBlank(chartType.getItem3())){
                    chartSpecOfItem3List = getChartSpecByChartId( etlConfD, chartType.getChartId(), chartType.getItem3());
                }
                long endTimeQ=System.currentTimeMillis();
                logger.info("获取 chartType.getChartId()对应data,item数据:{}----time:{} ms",chartType.getChartId(),(endTimeQ-startTimeQ));
                Map<String,ChartData> chartDataMap =new HashMap<>();
                Map<String,ChartSpec> chartSpecItem1Map =new HashMap<>();
                Map<String,ChartSpec> chartSpecItem2Map =new HashMap<>();
                Map<String,ChartSpec> chartSpecItem3Map =new HashMap<>();
                 if(chartSpecOfItem1List.size()>0){
                     for(ChartSpec chartSpecItem1:chartSpecOfItem1List){
                         chartSpecItem1Map.put(chartSpecItem1.getControlDataSeq(),chartSpecItem1);
                     }
                 }
                if(chartSpecOfItem2List.size()>0){
                    for(ChartSpec chartSpecItem2:chartSpecOfItem2List){
                        chartSpecItem2Map.put(chartSpecItem2.getControlDataSeq(),chartSpecItem2);
                    }
                }
                if(chartSpecOfItem3List.size()>0){
                    for(ChartSpec chartSpecItem3:chartSpecOfItem3List){
                        chartSpecItem3Map.put(chartSpecItem3.getControlDataSeq(),chartSpecItem3);
                    }
                }
                Map<String, List<ChartData>> defectTree =
                        chartDataList.stream().collect(groupingBy(ChartData::getMachineName));
                defectTree.forEach((machineName, defectTreeByMachineName) ->{
                    logger.info("***********************************************：",new Date());
                    long forEachsTart=System.currentTimeMillis();
                    defectTreeByMachineName.forEach(chartData -> {
                        List<String> tmpList = new ArrayList<>();
//                for(ChartData chartData:chartDataList){
                    ChartSpec chartSpecItem1=chartSpecItem1Map.get(chartData.getControlDataSeq());
                    ChartSpec chartSpecItem2=chartSpecItem2Map.get(chartData.getControlDataSeq());
                    ChartSpec chartSpecItem3=chartSpecItem3Map.get(chartData.getControlDataSeq());
                    chartDataMap.put(chartData.getControlDataSeq(),chartData);
                    logger.info("chartType.getChartId()={},chartData.getControlDataSeq()={},controlData.setEventtimePre={}",
                            chartData.getChartId(),chartData.getControlDataSeq(),
                            chartData.getProcProcessTime()==null?chartData.getProcessTime():chartData.getProcProcessTime());
                    ControlData controlData = new ControlData();
                    controlData.setChartid(chartType.getChartId());
                    controlData.setCharttype(chartType.getControlChartType());
                    controlData.setFactoryname(chartType.getFactoryName());
                    controlData.setSubfactoryname(chartType.getSubFatoryName());
                    controlData.setControldataseq(chartData.getControlDataSeq());
                    controlData.setDcdataid(chartData.getDcDataId());
//                    controlData.setDbTimestampQms();
                    controlData.setDbTimestampSrc(chartData.getTimeKey());
                    controlData.setGlassId(chartData.getMaterialName());
                    controlData.setEventtime(chartData.getEventTime());
                    controlData.setEventtimePre(chartData.getProcProcessTime()==null?chartData.getProcessTime():chartData.getProcProcessTime());
                    controlData.setItemname(chartData.getItemName());
                    controlData.setProductspecname(chartData.getProductSpecName());
                    controlData.setEccode(chartData.getEccode());
                    controlData.setProcessoperationname(chartData.getProcessOperationName());
                    controlData.setMachinename(chartData.getMachineName());
                    controlData.setUnitname(chartData.getUnitName());
                    controlData.setRecipename(chartData.getRecipeName());
                    if(chartType.getProcProcessOperationName()!=null && chartData.getProcProcessOperationName()!=null){
                        tmpList =Arrays.asList(chartData.getProcProcessOperationName().split(","));
                        if(tmpList.contains(chartType.getProcProcessOperationName())){
                            controlData.setProcprocessoperationname(chartType.getProcProcessOperationName());
                        }else{
                            controlData.setProcprocessoperationname(tmpList.get(0));
                        }
                    }
                        if(chartType.getProcRecipeName()!=null && chartData.getProcRecipeName()!=null){
                            tmpList =Arrays.asList(chartData.getProcRecipeName().split(","));
                            if(tmpList.contains(chartType.getProcRecipeName())){
                                controlData.setProcrecipename(chartType.getProcRecipeName());
                            }else{
                                controlData.setProcrecipename(tmpList.get(0));
                            }

                        }

                    if(chartType.getProcMachineName()!=null && chartData.getProcMachineName()!=null){
                        tmpList =Arrays.asList(chartData.getProcMachineName().split(","));
                        if(tmpList.contains(chartType.getProcMachineName())){
                            controlData.setProcmachinename(chartType.getProcMachineName());
                        }else{
                            controlData.setProcmachinename(tmpList.get(0));
                        }
                    }
                    if(chartType.getProcUnitName()!=null && chartData.getProcUnitName()!=null){
                        tmpList =Arrays.asList(chartData.getProcUnitName().split(","));
                        if(tmpList.contains(chartType.getProcUnitName())){
                            controlData.setProcunitname(chartType.getProcUnitName());
                        }else{
                            controlData.setProcunitname(tmpList.get(0));
                        }
                    }


                    controlData.setRunmode(chartData.getRunMode());
                    controlData.setOwnercode(chartData.getOwnerCode());
                    controlData.setRtc(chartData.getRtc());
                    controlData.setQtap(chartData.getQtap());
                    if(chartType.getItem1().equals("Xbar")||chartType.getItem1().equals("X")||chartType.getItem1().equals("I")||
                            chartType.getItem1().equals("Np")||chartType.getItem1().equals("P")||chartType.getItem1().equals("C")
                            ||chartType.getItem1().equals("U")){
                        controlData.setUspec(chartSpecItem1!=null?chartSpecItem1.getUpperSpecLimit():null);
                        controlData.setTarget(chartSpecItem1!=null?chartSpecItem1.getTarget():null);
                        controlData.setLspec(chartSpecItem1!=null?chartSpecItem1.getLowerSpecLimit():null);
                    }
                    controlData.setUcl1(chartSpecItem1!=null?chartSpecItem1.getUpperControlLimit():null);
                    controlData.setCl1(chartSpecItem1!=null?chartSpecItem1.getCenterLine():null);
                    controlData.setLcl1(chartSpecItem1!=null?chartSpecItem1.getLowerControlLimit():null);
                    controlData.setUcl2(chartSpecItem2!=null?chartSpecItem2.getUpperControlLimit():null);
                    controlData.setCl2(chartSpecItem2!=null?chartSpecItem2.getCenterLine():null);
                    controlData.setLcl2(chartSpecItem2!=null?chartSpecItem2.getLowerControlLimit():null);
                    controlData.setUcl3(chartSpecItem3!=null?chartSpecItem3.getUpperControlLimit():null);
                    controlData.setCl3(chartSpecItem3!=null?chartSpecItem3.getCenterLine():null);
                    controlData.setLcl3(chartSpecItem3!=null?chartSpecItem3.getLowerControlLimit():null);
                    try{
                        controlData.setdXbar(chartData.getxBar()!=null?Double.valueOf(chartData.getxBar()):null);
                    }catch(Exception e){
                        controlData.setdXbar(-123456.789);
                    }
                    try{
                        controlData.setdSdev(chartData.getS()!=null?Double.valueOf(chartData.getS()):null);
                    }catch(Exception e){
                        controlData.setdSdev(-123456.789);
                    }
                    try{
                        controlData.setdRng(chartData.getR()!=null?Double.valueOf(chartData.getR()):null);
                    }catch(Exception e){
                        controlData.setdRng(-123456.789);
                    }
                    try{
                        controlData.setdUnf1(chartData.getU1()!=null?Double.valueOf(chartData.getU1()):null);
                    }catch(Exception e){
                        controlData.setdUnf1(-123456.789);
                    }
                    try{
                        controlData.setdUnf2(chartData.getU2()!=null?Double.valueOf(chartData.getU2()):null);
                    }catch(Exception e){
                        controlData.setdUnf2(-123456.789);
                    }
                    try{
                        controlData.setdMax(chartData.getMAX()!=null?Double.valueOf(chartData.getMAX()):null);
                    }catch(Exception e){
                        controlData.setdMax(-123456.789);
                    }
                    try{
                        controlData.setdMin(chartData.getMIN()!=null?Double.valueOf(chartData.getMIN()):null);
                    }catch(Exception e){
                        controlData.setdMin(-123456.789);
                    }
                    try{
                        controlData.setdX(chartData.getI()!=null?Double.valueOf(chartData.getI()):null);
                    }catch(Exception e){
                        controlData.setdX(-123456.789);
                    }
                    try{
                        controlData.setdRm(chartData.getMR()!=null?Double.valueOf(chartData.getMR()):null);
                    }catch(Exception e){
                        controlData.setdRm(-123456.789);
                    }
                    try{
                        controlData.setdNp(chartData.getNP()!=null?Double.valueOf(chartData.getNP()):null);
                    }catch(Exception e){
                        controlData.setdNp(-123456.789);
                    }
                    try{
                        controlData.setdP(chartData.getP()!=null?Double.valueOf(chartData.getP()):null);
                    }catch(Exception e){
                        controlData.setdP(-123456.789);
                    }
                    try{
                        controlData.setdC(chartData.getC()!=null?Double.valueOf(chartData.getC()):null);
                    }catch(Exception e){
                        controlData.setdC(-123456.789);
                    }
                    try{
                        controlData.setdU(chartData.getU()!=null?Double.valueOf(chartData.getU()):null);
                    }catch(Exception e){
                        controlData.setdU(-123456.789);
                    }

                    controlData.setDelMark(chartSpecItem1!=null?chartSpecItem1.getMarkingFlag():null);
                    controlData.setOos(chartSpecItem1!=null?chartSpecItem1.getOosAlarmId():null);
                    controlData.setOoc1(chartSpecItem1!=null?chartSpecItem1.getOocAlarmId():null);
                    controlData.setOoc2(chartSpecItem2!=null?chartSpecItem2.getOocAlarmId():null);
                    controlData.setOoc3(chartSpecItem3!=null?chartSpecItem3.getOocAlarmId():null);
                    controlData.setOor1(chartSpecItem1!=null?chartSpecItem1.getOorAlarmId():null);
                    controlData.setOor2(chartSpecItem2!=null?chartSpecItem2.getOorAlarmId():null);
                    controlData.setOor3(chartSpecItem3!=null?chartSpecItem3.getOorAlarmId():null);
                    controlData.setSitecount(chartData.getSiteCount());
                    controlData.setRawdatas(rowDataToString(chartData));
                    controlDataList.add(controlData);
//                }
                    });

                //todo  将controlDataList保存
                    count[0]+= saveControlDataListbyChartId(etlConfD,controlDataList,machineName,tableNameList);
                logger.info("saveControlDataListbyChartId() getChartId()={},",chartType.getChartId());
                    long forEachsend=System.currentTimeMillis();
                    logger.info("foreach end time={},",forEachsend-forEachsTart);
                });
            }
            long endTime=System.currentTimeMillis();
            logger.info(" loadData for 循环结束 ，count :{}----time:{} ms",chartTypeList.size(),(endTime-startTime));
            logger.info(" LoadSpcDataService finish");
            return  count[0];
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException(e.getMessage());
        }

    }
   public List<String> getCharIdListByTime(EtlConfD etlConfD) throws Exception {   //根据条件获取CharId
 //      logger.info("执行getCharIdListByTime()");
       List<String> list =new ArrayList<>();
       String shopId =etlConfD.getShop();
       String eqpt   =etlConfD.getEqpt();
       String dcType =etlConfD.getCellType();
        StringBuffer str =new StringBuffer();
       try{
        str.append(" SELECT /*+ INDEX(CD CAPABILITYDATA_IDX1) */ distinct   CD.CHARTID  FROM   CONTROLDATA CD,  ChartDef CF  WHERE   Cf.chartid=CD.chartid ");
//        str.append("  AND CD.TIMEKEY >='201806042350'  AND CD.TIMEKEY < '201806050001' ");
      str.append(" AND CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())
                   +"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
       if(StringUtils.isNotBlank(shopId)){
           str.append("AND CF.Factoryname='"+shopId+"' ");
       }
           if(StringUtils.isNotBlank(eqpt)){
               str.append("AND CF.Machinename='"+eqpt+"' ");
           }

           str.append(" ORDER BY CD.CHARTID ");
            long startTime=System.currentTimeMillis();
            list=ora_spc.query(str.toString(),new ColumnListHandler<String>());
            long endTime=System.currentTimeMillis();
           logger.info("获取 chartid success count :{}----time:{} ms ----shop:{}",list.size(),(endTime-startTime),shopId);
            return list;
        }catch (Exception e){
            logger.error("获取 chartid error :{}----sql:{}",e,str.toString());
            throw new Exception("获取 chartid Exception:"+ e.getMessage());
        }

   }
   public List<ChartType> getChartTypeByChartId(EtlConfD etlConfD,List<String> charIdList)throws Exception{
 //      logger.info("执行getChartTypeByChartId()");
      List<ChartType> list =new ArrayList<>();
         String shopId =etlConfD.getShop();
         String eqpt   =etlConfD.getEqpt();   //todo
       String dcType =etlConfD.getCellType();

      StringBuffer str =new StringBuffer();
      String panelInCondition = set2InCondition(charIdList,"CtrlC.CHARTID");
      try{
          str.append(" SELECT CtrlC.CHARTID, CtrlC.CONTROLCHARTTYPE   ,CTD.FACTORYNAME, CTD.SUBFACTORYNAME,CTD.DCType,  CTD.PROCPROCESSOPERATIONNAME ,  ");
          str.append(" CTD.PROCMACHINENAME ,  CTD.PROCUNITNAME ,  CTD.PROCRECIPENAME ,EV.UDF1 AS ITEM1,EV.UDF2 AS ITEM2,EV.UDF3 AS ITEM3 ");
          str.append(" FROM  CONTROLCHART CtrlC, ENUMDEFVALUE EV, ChartDef CTD  WHERE  1=1 ");
 //         str.append(" and CtrlC.CHARTID = '495A1_A1870_WERP01_CD1_LOSS'  ");
          if(charIdList.size()>0 && charIdList.size()<1000){
              str.append(" and CtrlC.CHARTID in(").append(panelInCondition).append(") ");
          }else if(charIdList.size()>= 1000){
              str.append(" and( ").append(panelInCondition).append("  )");
          }

          str.append(" and CTD.FACTORYNAME ='"+shopId+"'  and CTD.DCType='"+dcType+"'     ");
          str.append(" AND EV .ENUMVALUE = CtrlC.CONTROLCHARTTYPE  and CTD.CHARTID =CtrlC.CHARTID  AND EV.ACTIVEFLAG = 'Y' and enumname='ControlChartType' ORDER BY CtrlC.CHARTID ");

          long startTime=System.currentTimeMillis();
          list=ora_spc.query(str.toString(),new BeanListHandler<ChartType>(ChartType.class));
          long endTime=System.currentTimeMillis();
          logger.info("获取 chartType success count :{}----time:{} ms",list.size(),(endTime-startTime));
          return list;
      }catch (Exception e){
          logger.error("获取 chartType error :{}----sql:{}",e.getMessage(),str.toString());
          throw new Exception("获取 chartType Exception:"+ e.getMessage());
      }


  }

    public List<ChartType> getChartTypeByChartId(EtlConfD etlConfD)throws Exception{
        //      logger.info("执行getChartTypeByChartId()");
        List<ChartType> list =new ArrayList<>();
        String shopId =etlConfD.getShop();
        int type   = Integer.parseInt(etlConfD.getExt1());
        String eqpt   =etlConfD.getEqpt();
        String dcType =etlConfD.getCellType();

        StringBuffer str =new StringBuffer();
        try{
            str.append(" SELECT CtrlC.CHARTID, CtrlC.CONTROLCHARTTYPE   ,CTD.FACTORYNAME, CTD.SUBFACTORYNAME,CTD.DCType,  CTD.PROCPROCESSOPERATIONNAME ,  ");
            str.append(" CTD.PROCMACHINENAME ,  CTD.PROCUNITNAME ,  CTD.PROCRECIPENAME ,EV.UDF1 AS ITEM1,EV.UDF2 AS ITEM2,EV.UDF3 AS ITEM3 ");
            str.append(" FROM  CONTROLCHART CtrlC, ENUMDEFVALUE EV, ChartDef CTD  WHERE  1=1 ");
            //         str.append(" and CtrlC.CHARTID = '495A1_A1870_WERP01_CD1_LOSS'  ");
                str.append(" and CtrlC.CHARTID in( SELECT /*+ INDEX(CD CAPABILITYDATA_IDX1) */ distinct   CD.CHARTID  FROM   CONTROLDATA CD,  ChartDef CF  WHERE   Cf.chartid=CD.chartid ");
            str.append(" AND CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
            if(StringUtils.isNotBlank(shopId)){
                str.append("AND CF.Factoryname='"+shopId+"' ");
            }
            if(StringUtils.isNotBlank(eqpt) && type==0){
                str.append("AND CF.Machinename in( "+eqpt+") ");
            }
            if(StringUtils.isNotBlank(eqpt) && type==1){

                str.append("AND  regexp_like(TrackOut.PROCESSOPERATIONNAME ,'^"+eqpt+"') ");
//                str.append("AND CF.Machinename='"+eqpt+"' ");
            }
            str.append(" )and CTD.FACTORYNAME ='"+shopId+"'  and CTD.DCType='"+dcType+"'     ");
            str.append(" AND EV .ENUMVALUE = CtrlC.CONTROLCHARTTYPE  and CTD.CHARTID =CtrlC.CHARTID  AND EV.ACTIVEFLAG = 'Y' and enumname='ControlChartType' ORDER BY CtrlC.CHARTID ");

            long startTime=System.currentTimeMillis();
            list=ora_spc.query(str.toString(),new BeanListHandler<ChartType>(ChartType.class));
            long endTime=System.currentTimeMillis();
            logger.info("获取 chartType success count :{}----time:{} ms",list.size(),(endTime-startTime));
            return list;
        }catch (Exception e){
            logger.error("获取 chartType error :{}----sql:{}",e.getMessage(),str.toString());
            throw new Exception("获取 chartType Exception:"+ e.getMessage());
        }


    }
    public List<ChartData> getChartDataByChartId(EtlConfD etlConfD, String chartId)throws Exception{
  //      logger.info("执行getChartDataByChartId {}",chartId);
        List<ChartData> list =new ArrayList<>();
        String sql ="";
        StringBuffer str =new StringBuffer();
//        String panelInCondition = set2InCondition(charIdList,"CD.CHARTID");
        try{
           sql+=" SELECT                                                                                    "+
                   " 	*                                                                                       "+
                   " FROM                                                                                      "+
                   " 	(                                                                                       "+
                   " 		SELECT                                                                              "+
                   " 			AA.*                                                                            "+
                   " 		FROM                                                                                "+
                   " 			(                                                                               "+
                   " 				SELECT A.CHARTID,                                                           "+
                   " 					A.CONTROLDATASEQ,                                                "+
                  " 					TO_DATE( SUBSTR(A.TIMEKEY,0,14), 'YYYY-MM-DD HH24:MI:SS' ) AS TIMEKEY,                                                              "+
                   " 					DD.DCDATAID,                                                            "+
                   " 					DD.MATERIALNAME,                                                        "+
                   " 					TO_CHAR( DD.PROCESSTIME, 'YYYY-MM-DD HH24:MI:SS' ) AS PROCESSTIME,      "+
                   " 					TO_CHAR( DD.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS' ) AS EVENTTIME,          "+
                   " 					CASE                                                                    "+
                   " 						WHEN MD.MARKINGTIME IS NOT NULL THEN 'Y'                            "+
                   " 						ELSE 'N'                                                            "+
                   " 					END AS MARKINGFLAG,                                                     "+
                   " 					DD.PRODUCTSPECNAME,                                                     "+
                   " 					DD.ECCODE,                                                              "+
                   " 					DD.PROCESSOPERATIONNAME,                                                "+
                   " 					DD.MACHINENAME,                                                         "+
                   " 					DD.UNITNAME,                                                            "+
                   " 					DD.SUBUNITNAME,                                                         "+
                   " 					DD.RECIPENAME,                                                          "+
                   " 					DD.RUNMODE,                                                             "+
                   " 					DD.OWNERCODE,                                                           "+
                   " 					DD.RTC,                                                                 "+
                   " 					DD.QTAP,                                                                "+
                   " 					DI.ITEMNAME,                                                            "+
                   " 					DI.ALIASITEMNAME AS ALIASITEMNAME,                                      "+
                   " 					LISTAGG(                                                                "+
                   " 						DR.PROCPROCESSOPERATIONNAME,                                        "+
                   " 						','                                                                 "+
                   " 					) WITHIN GROUP(                                                         "+
                   " 					ORDER BY                                                                "+
                   " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                   " 					) AS PROCPROCESSOPERATIONNAME,                                          "+
                   " 					LISTAGG(                                                                "+
                   " 						DR.PROCRECIPENAME,                                                  "+
                   " 						','                                                                 "+
                   " 					) WITHIN GROUP(                                                         "+
                   " 					ORDER BY                                                                "+
                   " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                   " 					) AS PROCRECIPENAME,                                                    "+
                   " 					LISTAGG(                                                                "+
                   " 						DR.PROCMACHINENAME,                                                 "+
                   " 						','                                                                 "+
                   " 					) WITHIN GROUP(                                                         "+
                   " 					ORDER BY                                                                "+
                   " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                   " 					) AS PROCMACHINENAME,                                                   "+
                   " 					LISTAGG(                                                                "+
                   " 						DR.PROCUNITNAME,                                                    "+
                   " 						','                                                                 "+
                   " 					) WITHIN GROUP(                                                         "+
                   " 					ORDER BY                                                                "+
                   " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                   " 					) AS PROCUNITNAME,                                                      "+
                   " 					LISTAGG(                                                                "+
                   " 						DR.PROCSUBUNITNAME,                                                 "+
                   " 						','                                                                 "+
                   " 					) WITHIN GROUP(                                                         "+
                   " 					ORDER BY                                                                "+
                   " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                   " 					) AS PROCSUBUNITNAME,                                                   "+
                   " 					MAX( A.ALARMTYPE ) AS ALARMTYPE,                                        "+
                   " 					A.XBAR,                                                                 "+
                   " 					A.S,                                                                    "+
                   " 					A.R,                                                                    "+
                   " 					A.U1,                                                                   "+
                   " 					A.U2,                                                                   "+
                   " 					A.MIN,                                                                  "+
                   " 					A.MAX,                                                                  "+
                   " 					A.I,                                                                    "+
                   " 					A.MR,                                                                   "+
                   " 					A.NP,                                                                   "+
                   " 					A.P,                                                                    "+
                   " 					A.C,                                                                    "+
                   " 					A.U,                                                                    "+
                   " 					COUNT( CDR.SITENAME ) OVER(                                             "+
                   " 						PARTITION BY A.CONTROLDATASEQ                                       "+
                   " 					) AS SITECOUNT,                                                         "+
                   " 					'RAWDATA' || LPAD( RANK() OVER( PARTITION BY A.CONTROLDATASEQ ORDER BY CDR.SITENAME ), 3, '0' ) AS SITENAMESEQ,"+
                   " 					CDR.RESULT AS SITERESULT                                              "+
                   " 				FROM                                                                      "+
                   " 					(                                                                     "+
                   " 						SELECT                                                            "+
                   " 							*                                                             "+
                   " 						FROM                                                              "+
                   " 							(                                                             "+
                   " 								SELECT                                                    "+
                   " 									CD.CHARTID,                                           "+
                   " 									CD.CONTROLDATASEQ,                                    "+
                   " 									CD.DCDATAID,                                          "+
                   " 									CD.TIMEKEY,                                           "+
                   " 									CDS.CHARTNAME,                                        "+
                   " 									NVL( CDR.RESULT, 'NA' ) AS RESULT,                    "+
                   " 									MAX( MAX( AR.ALARMTYPE )) OVER(                       "+
                   " 										PARTITION BY CD.CHARTID,                          "+
                   " 										CD.CONTROLDATASEQ,                                "+
                   " 										CD.DCDATAID                                       "+
                   " 									) AS ALARMTYPE                                        "+
                   " 								FROM                                                      "+
                   " 									(                                                     "+
                   " 										SELECT                                            "+
                   " 											/*+ INDEX(CD CONTROLDATA_IDX1) */               "+
                   " 											ROWNUM AS DATASEQ,                            "+
                   " 											CD.CHARTID,                                   "+
                   " 											CD.CONTROLDATASEQ,                            "+
                   " 											CD.DCDATAID,                                  "+
                   " 											CD.TIMEKEY                                    "+
                   " 										FROM                                              "+
                   " 											CONTROLDATA CD                                "+
                   " 										WHERE  1=1                                        ";
//         str.append(" and CD.CHARTID = 'PIPR03-PIIN-495P2-U'  ");
            str.append(" and CD.CHARTID = '"+chartId+"'  ");
//            str.append(" and CD.controldataseq = '20180601000125817001'  ");
 //           str.append(" AND CD.TIMEKEY >= '20180601000000' AND CD.TIMEKEY < '20180601000500' ");
            str.append(" and CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())
                    +"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
            sql+=str.toString();

            sql+=	" 								) CD                                          "+
                    " 							INNER JOIN CONTROLDATASPEC CDS ON                 "+
                    " 								CDS.CHARTID = CD.CHARTID                      "+
                    " 								AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ    "+
                    " 							INNER JOIN ENUMDEFVALUE EDV ON                    "+
                    " 								EDV.ENUMNAME = CDS.CONTROLCHARTTYPE           "+
                    " 								AND EDV.ENUMVALUE = CDS.CHARTNAME             "+
                    " 							LEFT JOIN CONTROLDATARESULT CDR ON                "+
                    " 								CDR.CHARTID = CD.CHARTID                      "+
                    " 								AND CDR.CONTROLDATASEQ = CD.CONTROLDATASEQ    "+
                    " 								AND CDR.SITENAME = EDV.UDF1                   "+
                    " 							LEFT JOIN ALARMDATA AR ON                         "+
                    " 								CDR.CHARTID = AR.CHARTID                      "+
                    " 								AND CDR.CONTROLDATASEQ = AR.CONTROLDATASEQ    "+
                    " 								AND EDV.UDF1 = AR.CHARTNAME                   "+
                    " 							WHERE                                             "+
                    " 								1 = 1                                         "+
                    " 							GROUP BY                                          "+
                    " 								CD.CHARTID,                                   "+
                    " 								CD.CONTROLDATASEQ,                            "+
                    " 								CD.DCDATAID,                                  "+
                    " 								CD.TIMEKEY,                                   "+
                    " 								CDS.CHARTNAME,                                "+
                    " 								NVL( CDR.RESULT, 'NA' )                       "+
                    " 						) PIVOT(                                              "+
                    " 							MAX( RESULT ) FOR CHARTNAME IN(                   "+
                    " 								'Xbar' AS XBAR,                               "+
                    " 								'S' AS S,                                     "+
                    " 								'R' AS R,                                     "+
                    " 								'U1' AS U1,                                   "+
                    " 								'U2' AS U2,                                   "+
                    " 								'Min' AS MIN,                                 "+
                    " 								'Max' AS MAX,                                 "+
                    " 								'I' AS I,                                     "+
                    " 								'mR' AS MR,                                   "+
                    " 								'NP' AS NP,                                   "+
                    " 								'P' AS P,                                     "+
                    " 								'C' AS C,                                     "+
                    " 								'U' AS U                                      "+
                    " 							)                                                 "+
                    " 						)                                                     "+
                    " 					ORDER BY                                                  "+
                    " 						CONTROLDATASEQ                                        "+
                    " 				) A                                                           "+
                    " 			INNER JOIN CHARTDEF CH ON                                         "+
                    " 				CH.CHARTID = A.CHARTID                                        "+
                    " 			INNER JOIN DCDATA DD ON                                           "+
                    " 				DD.DCDATAID = A.DCDATAID                                      "+
                    " 			LEFT JOIN DCDEF DC ON                                             "+
                    " 				DC.FACTORYNAME = DD.FACTORYNAME                               "+
                    " 				AND DC.MACHINENAME = DD.MACHINENAME                           "+
                    " 				AND NVL( DC.UNITNAME, '-' )= NVL( DD.UNITNAME, '-' )          "+
                    " 			LEFT JOIN DCITEM DI ON                                            "+
                    " 				DI.DCDEFNAME = DC.DCDEFNAME                                   "+
                    " 				AND DI.ALIASITEMNAME = CH.ALIASITEMNAME                       "+
                    " 			LEFT JOIN MARKINGDATA MD ON                                       "+
                    " 				MD.CHARTID = A.CHARTID                                        "+
                    " 				AND MD.CONTROLDATASEQ = A.CONTROLDATASEQ                      "+
                    " 			LEFT JOIN DCPOREFERENCEDATA DR ON                                 "+
                    " 				DR.DCDATAID = A.DCDATAID                                      "+
                    " 			LEFT JOIN CONTROLDATARESULT CDR ON                                "+
                    " 				CDR.CHARTID = A.CHARTID                                       "+
                    " 				AND CDR.CONTROLDATASEQ = A.CONTROLDATASEQ                     "+
                    " 				AND CDR.SITETYPE = 'Raw'                                      "+
                    " 			GROUP BY                                                          "+
                    " 			A.CHARTID,                                                        "+
                    " 				A.CONTROLDATASEQ,                                             "+
                    " 				A.TIMEKEY,                                                    "+
                    " 				DD.DCDATAID,                                                  "+
                    " 				DD.MATERIALNAME,                                              "+
                    " 				DD.PROCESSTIME,                                               "+
                    " 				DD.EVENTTIME,                                                 "+
                    " 				MD.MARKINGTIME,                                               "+
                    " 				DD.PRODUCTSPECNAME,                                           "+
                    " 				DD.ECCODE,                                                    "+
                    " 				DD.PROCESSOPERATIONNAME,                                      "+
                    " 				DD.MACHINENAME,                                               "+
                    " 				DD.UNITNAME,                                                  "+
                    " 				DD.SUBUNITNAME,                                               "+
                    " 				DD.RECIPENAME,                                                "+
                    " 				DD.RUNMODE,                                                   "+
                    " 				DD.OWNERCODE,                                                 "+
                    " 				DD.RTC,                                                       "+
                    " 				DD.QTAP,                                                      "+
                    " 				DI.ITEMNAME,                                                  "+
                    " 				DI.ALIASITEMNAME,                                             "+
                    " 				A.XBAR,                                                       "+
                    " 				A.S,                                                          "+
                    " 				A.R,                                                          "+
                    " 				A.U1,                                                         "+
                    " 				A.U2,                                                         "+
                    " 				A.MIN,                                                        "+
                    " 				A.MAX,                                                        "+
                    " 				A.I,                                                          "+
                    " 				A.MR,                                                         "+
                    " 				A.NP,                                                         "+
                    " 				A.P,                                                          "+
                    " 				A.C,                                                          "+
                    " 				A.U,                                                          "+
                    " 				CDR.SITENAME,                                                 "+
                    " 				CDR.RESULT                                                    "+
                    " 		) AA                                                                  "+
                    " ) PIVOT(                                                                    "+
                    "	MAX( SITERESULT ) FOR SITENAMESEQ IN(                                     "+
                    " 'RAWDATA001' AS RAWDATA001, 'RAWDATA002' AS RAWDATA002, 'RAWDATA003' AS RAWDATA003, 'RAWDATA004' AS RAWDATA004,                      "+
                    " 'RAWDATA005' AS RAWDATA005, 'RAWDATA006' AS RAWDATA006, 'RAWDATA007' AS RAWDATA007, 'RAWDATA008' AS RAWDATA008,                        "+
                    " 'RAWDATA009' AS RAWDATA009, 'RAWDATA010' AS RAWDATA010, 'RAWDATA011' AS RAWDATA011, 'RAWDATA012' AS RAWDATA012,                        "+
                    " 'RAWDATA013' AS RAWDATA013, 'RAWDATA014' AS RAWDATA014, 'RAWDATA015' AS RAWDATA015, 'RAWDATA016' AS RAWDATA016,                        "+
                    " 'RAWDATA017' AS RAWDATA017, 'RAWDATA018' AS RAWDATA018, 'RAWDATA019' AS RAWDATA019, 'RAWDATA020' AS RAWDATA020,                        "+
                    " 'RAWDATA021' AS RAWDATA021, 'RAWDATA022' AS RAWDATA022, 'RAWDATA023' AS RAWDATA023, 'RAWDATA024' AS RAWDATA024,                        "+
                    " 'RAWDATA025' AS RAWDATA025, 'RAWDATA026' AS RAWDATA026, 'RAWDATA027' AS RAWDATA027, 'RAWDATA028' AS RAWDATA028,                        "+
                    " 'RAWDATA029' AS RAWDATA029, 'RAWDATA030' AS RAWDATA030, 'RAWDATA031' AS RAWDATA031, 'RAWDATA032' AS RAWDATA032,                        "+
                    " 'RAWDATA033' AS RAWDATA033, 'RAWDATA034' AS RAWDATA034, 'RAWDATA035' AS RAWDATA035, 'RAWDATA036' AS RAWDATA036,                        "+
                    " 'RAWDATA037' AS RAWDATA037, 'RAWDATA038' AS RAWDATA038, 'RAWDATA039' AS RAWDATA039, 'RAWDATA040' AS RAWDATA040,                        "+
                    " 'RAWDATA041' AS RAWDATA041, 'RAWDATA042' AS RAWDATA042, 'RAWDATA043' AS RAWDATA043, 'RAWDATA044' AS RAWDATA044,                        "+
                    " 'RAWDATA045' AS RAWDATA045, 'RAWDATA046' AS RAWDATA046, 'RAWDATA047' AS RAWDATA047, 'RAWDATA048' AS RAWDATA048,                        "+
                    " 'RAWDATA049' AS RAWDATA049, 'RAWDATA050' AS RAWDATA050, 'RAWDATA051' AS RAWDATA051, 'RAWDATA052' AS RAWDATA052,                        "+
                    " 'RAWDATA053' AS RAWDATA053, 'RAWDATA054' AS RAWDATA054, 'RAWDATA055' AS RAWDATA055, 'RAWDATA056' AS RAWDATA056,                        "+
                    " 'RAWDATA057' AS RAWDATA057, 'RAWDATA058' AS RAWDATA058, 'RAWDATA059' AS RAWDATA059, 'RAWDATA060' AS RAWDATA060,                        "+
                    " 'RAWDATA061' AS RAWDATA061, 'RAWDATA062' AS RAWDATA062, 'RAWDATA063' AS RAWDATA063, 'RAWDATA064' AS RAWDATA064,                        "+
                    " 'RAWDATA065' AS RAWDATA065, 'RAWDATA066' AS RAWDATA066, 'RAWDATA067' AS RAWDATA067, 'RAWDATA068' AS RAWDATA068,                        "+
                    " 'RAWDATA069' AS RAWDATA069, 'RAWDATA070' AS RAWDATA070, 'RAWDATA071' AS RAWDATA071, 'RAWDATA072' AS RAWDATA072,                        "+
                    " 'RAWDATA073' AS RAWDATA073, 'RAWDATA074' AS RAWDATA074, 'RAWDATA075' AS RAWDATA075, 'RAWDATA076' AS RAWDATA076,                        "+
                    " 'RAWDATA077' AS RAWDATA077, 'RAWDATA078' AS RAWDATA078, 'RAWDATA079' AS RAWDATA079, 'RAWDATA080' AS RAWDATA080,                        "+
                    " 'RAWDATA081' AS RAWDATA081, 'RAWDATA082' AS RAWDATA082, 'RAWDATA083' AS RAWDATA083, 'RAWDATA084' AS RAWDATA084,                        "+
                    " 'RAWDATA085' AS RAWDATA085, 'RAWDATA086' AS RAWDATA086, 'RAWDATA087' AS RAWDATA087, 'RAWDATA088' AS RAWDATA088,                        "+
                    " 'RAWDATA089' AS RAWDATA089, 'RAWDATA090' AS RAWDATA090, 'RAWDATA091' AS RAWDATA091, 'RAWDATA092' AS RAWDATA092,                        "+
                    " 'RAWDATA093' AS RAWDATA093, 'RAWDATA094' AS RAWDATA094, 'RAWDATA095' AS RAWDATA095, 'RAWDATA096' AS RAWDATA096,                        "+
                    " 'RAWDATA097' AS RAWDATA097, 'RAWDATA098' AS RAWDATA098, 'RAWDATA099' AS RAWDATA099, 'RAWDATA100' AS RAWDATA100,                        "+
                    " 'RAWDATA101' AS RAWDATA101, 'RAWDATA102' AS RAWDATA102, 'RAWDATA103' AS RAWDATA103, 'RAWDATA104' AS RAWDATA104,                        "+
                    " 'RAWDATA105' AS RAWDATA105, 'RAWDATA106' AS RAWDATA106, 'RAWDATA107' AS RAWDATA107, 'RAWDATA108' AS RAWDATA108,                        "+
                    " 'RAWDATA109' AS RAWDATA109, 'RAWDATA110' AS RAWDATA110, 'RAWDATA111' AS RAWDATA111, 'RAWDATA112' AS RAWDATA112,                        "+
                    " 'RAWDATA113' AS RAWDATA113, 'RAWDATA114' AS RAWDATA114, 'RAWDATA115' AS RAWDATA115, 'RAWDATA116' AS RAWDATA116,                        "+
                    " 'RAWDATA117' AS RAWDATA117, 'RAWDATA118' AS RAWDATA118, 'RAWDATA119' AS RAWDATA119, 'RAWDATA120' AS RAWDATA120,                        "+
                    " 'RAWDATA121' AS RAWDATA121, 'RAWDATA122' AS RAWDATA122, 'RAWDATA123' AS RAWDATA123, 'RAWDATA124' AS RAWDATA124,                        "+
                    " 'RAWDATA125' AS RAWDATA125, 'RAWDATA126' AS RAWDATA126, 'RAWDATA127' AS RAWDATA127, 'RAWDATA128' AS RAWDATA128,                        "+
                    " 'RAWDATA129' AS RAWDATA129, 'RAWDATA130' AS RAWDATA130, 'RAWDATA131' AS RAWDATA131, 'RAWDATA132' AS RAWDATA132,                        "+
                    " 'RAWDATA133' AS RAWDATA133, 'RAWDATA134' AS RAWDATA134, 'RAWDATA135' AS RAWDATA135, 'RAWDATA136' AS RAWDATA136,                        "+
                    " 'RAWDATA137' AS RAWDATA137, 'RAWDATA138' AS RAWDATA138, 'RAWDATA139' AS RAWDATA139, 'RAWDATA140' AS RAWDATA140,                        "+
                    " 'RAWDATA141' AS RAWDATA141, 'RAWDATA142' AS RAWDATA142, 'RAWDATA143' AS RAWDATA143, 'RAWDATA144' AS RAWDATA144,                        "+
                    " 'RAWDATA145' AS RAWDATA145, 'RAWDATA146' AS RAWDATA146, 'RAWDATA147' AS RAWDATA147, 'RAWDATA148' AS RAWDATA148,                        "+
                    " 'RAWDATA149' AS RAWDATA149, 'RAWDATA150' AS RAWDATA150, 'RAWDATA151' AS RAWDATA151, 'RAWDATA152' AS RAWDATA152,                        "+
                    " 'RAWDATA153' AS RAWDATA153, 'RAWDATA154' AS RAWDATA154, 'RAWDATA155' AS RAWDATA155, 'RAWDATA156' AS RAWDATA156,                        "+
                    " 'RAWDATA157' AS RAWDATA157, 'RAWDATA158' AS RAWDATA158, 'RAWDATA159' AS RAWDATA159, 'RAWDATA160' AS RAWDATA160,                        "+
                    " 'RAWDATA161' AS RAWDATA161, 'RAWDATA162' AS RAWDATA162, 'RAWDATA163' AS RAWDATA163, 'RAWDATA164' AS RAWDATA164,                        "+
                    " 'RAWDATA165' AS RAWDATA165, 'RAWDATA166' AS RAWDATA166, 'RAWDATA167' AS RAWDATA167, 'RAWDATA168' AS RAWDATA168,                        "+
                    " 'RAWDATA169' AS RAWDATA169, 'RAWDATA170' AS RAWDATA170, 'RAWDATA171' AS RAWDATA171, 'RAWDATA172' AS RAWDATA172,                        "+
                    " 'RAWDATA173' AS RAWDATA173, 'RAWDATA174' AS RAWDATA174, 'RAWDATA175' AS RAWDATA175, 'RAWDATA176' AS RAWDATA176,                        "+
                    " 'RAWDATA177' AS RAWDATA177, 'RAWDATA178' AS RAWDATA178, 'RAWDATA179' AS RAWDATA179, 'RAWDATA180' AS RAWDATA180,                        "+
                    " 'RAWDATA181' AS RAWDATA181, 'RAWDATA182' AS RAWDATA182, 'RAWDATA183' AS RAWDATA183, 'RAWDATA184' AS RAWDATA184,                        "+
                    " 'RAWDATA185' AS RAWDATA185, 'RAWDATA186' AS RAWDATA186, 'RAWDATA187' AS RAWDATA187, 'RAWDATA188' AS RAWDATA188,                        "+
                    " 'RAWDATA189' AS RAWDATA189, 'RAWDATA190' AS RAWDATA190, 'RAWDATA191' AS RAWDATA191, 'RAWDATA192' AS RAWDATA192,                        "+
                    " 'RAWDATA193' AS RAWDATA193, 'RAWDATA194' AS RAWDATA194, 'RAWDATA195' AS RAWDATA195, 'RAWDATA196' AS RAWDATA196,                        "+
                    " 'RAWDATA197' AS RAWDATA197, 'RAWDATA198' AS RAWDATA198, 'RAWDATA199' AS RAWDATA199, 'RAWDATA200' AS RAWDATA200,                        "+
                    " 'RAWDATA201' AS RAWDATA201, 'RAWDATA202' AS RAWDATA202, 'RAWDATA203' AS RAWDATA203, 'RAWDATA204' AS RAWDATA204,                        "+
                    " 'RAWDATA205' AS RAWDATA205, 'RAWDATA206' AS RAWDATA206, 'RAWDATA207' AS RAWDATA207, 'RAWDATA208' AS RAWDATA208,                        "+
                    " 'RAWDATA209' AS RAWDATA209, 'RAWDATA210' AS RAWDATA210, 'RAWDATA211' AS RAWDATA211, 'RAWDATA212' AS RAWDATA212,                        "+
                    " 'RAWDATA213' AS RAWDATA213, 'RAWDATA214' AS RAWDATA214, 'RAWDATA215' AS RAWDATA215, 'RAWDATA216' AS RAWDATA216,                        "+
                    " 'RAWDATA217' AS RAWDATA217, 'RAWDATA218' AS RAWDATA218, 'RAWDATA219' AS RAWDATA219, 'RAWDATA220' AS RAWDATA220,                        "+
                    " 'RAWDATA221' AS RAWDATA221, 'RAWDATA222' AS RAWDATA222, 'RAWDATA223' AS RAWDATA223, 'RAWDATA224' AS RAWDATA224,                        "+
                    " 'RAWDATA225' AS RAWDATA225, 'RAWDATA226' AS RAWDATA226, 'RAWDATA227' AS RAWDATA227, 'RAWDATA228' AS RAWDATA228,                        "+
                    " 'RAWDATA229' AS RAWDATA229, 'RAWDATA230' AS RAWDATA230, 'RAWDATA231' AS RAWDATA231, 'RAWDATA232' AS RAWDATA232,                        "+
                    " 'RAWDATA233' AS RAWDATA233, 'RAWDATA234' AS RAWDATA234, 'RAWDATA235' AS RAWDATA235, 'RAWDATA236' AS RAWDATA236,                        "+
                    " 'RAWDATA237' AS RAWDATA237, 'RAWDATA238' AS RAWDATA238, 'RAWDATA239' AS RAWDATA239, 'RAWDATA240' AS RAWDATA240,                        "+
                    " 'RAWDATA241' AS RAWDATA241, 'RAWDATA242' AS RAWDATA242, 'RAWDATA243' AS RAWDATA243, 'RAWDATA244' AS RAWDATA244,                        "+
                    " 'RAWDATA245' AS RAWDATA245, 'RAWDATA246' AS RAWDATA246, 'RAWDATA247' AS RAWDATA247, 'RAWDATA248' AS RAWDATA248,                        "+
                    " 'RAWDATA249' AS RAWDATA249, 'RAWDATA250' AS RAWDATA250, 'RAWDATA251' AS RAWDATA251, 'RAWDATA252' AS RAWDATA252,                        "+
                    " 'RAWDATA253' AS RAWDATA253, 'RAWDATA254' AS RAWDATA254, 'RAWDATA255' AS RAWDATA255, 'RAWDATA256' AS RAWDATA256,                        "+
                    " 'RAWDATA257' AS RAWDATA257, 'RAWDATA258' AS RAWDATA258, 'RAWDATA259' AS RAWDATA259, 'RAWDATA260' AS RAWDATA260,                        "+
                    " 'RAWDATA261' AS RAWDATA261, 'RAWDATA262' AS RAWDATA262, 'RAWDATA263' AS RAWDATA263, 'RAWDATA264' AS RAWDATA264,                        "+
                    " 'RAWDATA265' AS RAWDATA265, 'RAWDATA266' AS RAWDATA266, 'RAWDATA267' AS RAWDATA267, 'RAWDATA268' AS RAWDATA268,                        "+
                    " 'RAWDATA269' AS RAWDATA269, 'RAWDATA270' AS RAWDATA270, 'RAWDATA271' AS RAWDATA271, 'RAWDATA272' AS RAWDATA272,                        "+
                    " 'RAWDATA273' AS RAWDATA273, 'RAWDATA274' AS RAWDATA274, 'RAWDATA275' AS RAWDATA275, 'RAWDATA276' AS RAWDATA276,                        "+
                    " 'RAWDATA277' AS RAWDATA277, 'RAWDATA278' AS RAWDATA278, 'RAWDATA279' AS RAWDATA279, 'RAWDATA280' AS RAWDATA280,                        "+
                    " 'RAWDATA281' AS RAWDATA281, 'RAWDATA282' AS RAWDATA282, 'RAWDATA283' AS RAWDATA283, 'RAWDATA284' AS RAWDATA284,                        "+
                    " 'RAWDATA285' AS RAWDATA285, 'RAWDATA286' AS RAWDATA286, 'RAWDATA287' AS RAWDATA287, 'RAWDATA288' AS RAWDATA288,                        "+
                    " 'RAWDATA289' AS RAWDATA289, 'RAWDATA290' AS RAWDATA290, 'RAWDATA291' AS RAWDATA291, 'RAWDATA292' AS RAWDATA292,                        "+
                    " 'RAWDATA293' AS RAWDATA293, 'RAWDATA294' AS RAWDATA294, 'RAWDATA295' AS RAWDATA295, 'RAWDATA296' AS RAWDATA296,                        "+
                    " 'RAWDATA297' AS RAWDATA297, 'RAWDATA298' AS RAWDATA298, 'RAWDATA299' AS RAWDATA299, 'RAWDATA300' AS RAWDATA300,                        "+
                    " 'RAWDATA301' AS RAWDATA301, 'RAWDATA302' AS RAWDATA302, 'RAWDATA303' AS RAWDATA303, 'RAWDATA304' AS RAWDATA304,                        "+
                    " 'RAWDATA305' AS RAWDATA305, 'RAWDATA306' AS RAWDATA306, 'RAWDATA307' AS RAWDATA307, 'RAWDATA308' AS RAWDATA308,                        "+
                    " 'RAWDATA309' AS RAWDATA309, 'RAWDATA310' AS RAWDATA310, 'RAWDATA311' AS RAWDATA311, 'RAWDATA312' AS RAWDATA312,                        "+
                    " 'RAWDATA313' AS RAWDATA313, 'RAWDATA314' AS RAWDATA314, 'RAWDATA315' AS RAWDATA315, 'RAWDATA316' AS RAWDATA316,                        "+
                    " 'RAWDATA317' AS RAWDATA317, 'RAWDATA318' AS RAWDATA318, 'RAWDATA319' AS RAWDATA319, 'RAWDATA320' AS RAWDATA320,                        "+
                    " 'RAWDATA321' AS RAWDATA321, 'RAWDATA322' AS RAWDATA322, 'RAWDATA323' AS RAWDATA323, 'RAWDATA324' AS RAWDATA324,                        "+
                    " 'RAWDATA325' AS RAWDATA325, 'RAWDATA326' AS RAWDATA326, 'RAWDATA327' AS RAWDATA327, 'RAWDATA328' AS RAWDATA328,                        "+
                    " 'RAWDATA329' AS RAWDATA329, 'RAWDATA330' AS RAWDATA330, 'RAWDATA331' AS RAWDATA331, 'RAWDATA332' AS RAWDATA332,                        "+
                    " 'RAWDATA333' AS RAWDATA333, 'RAWDATA334' AS RAWDATA334, 'RAWDATA335' AS RAWDATA335, 'RAWDATA336' AS RAWDATA336,                        "+
                    " 'RAWDATA337' AS RAWDATA337, 'RAWDATA338' AS RAWDATA338, 'RAWDATA339' AS RAWDATA339, 'RAWDATA340' AS RAWDATA340,                        "+
                    " 'RAWDATA341' AS RAWDATA341, 'RAWDATA342' AS RAWDATA342, 'RAWDATA343' AS RAWDATA343, 'RAWDATA344' AS RAWDATA344,                        "+
                    " 'RAWDATA345' AS RAWDATA345, 'RAWDATA346' AS RAWDATA346, 'RAWDATA347' AS RAWDATA347, 'RAWDATA348' AS RAWDATA348,                        "+
                    " 'RAWDATA349' AS RAWDATA349, 'RAWDATA350' AS RAWDATA350, 'RAWDATA351' AS RAWDATA351, 'RAWDATA352' AS RAWDATA352,                        "+
                    " 'RAWDATA353' AS RAWDATA353, 'RAWDATA354' AS RAWDATA354, 'RAWDATA355' AS RAWDATA355, 'RAWDATA356' AS RAWDATA356,                        "+
                    " 'RAWDATA357' AS RAWDATA357, 'RAWDATA358' AS RAWDATA358, 'RAWDATA359' AS RAWDATA359, 'RAWDATA360' AS RAWDATA360,                        "+
                    " 'RAWDATA361' AS RAWDATA361, 'RAWDATA362' AS RAWDATA362, 'RAWDATA363' AS RAWDATA363, 'RAWDATA364' AS RAWDATA364,                        "+
                    " 'RAWDATA365' AS RAWDATA365, 'RAWDATA366' AS RAWDATA366, 'RAWDATA367' AS RAWDATA367, 'RAWDATA368' AS RAWDATA368,                        "+
                    " 'RAWDATA369' AS RAWDATA369, 'RAWDATA370' AS RAWDATA370, 'RAWDATA371' AS RAWDATA371, 'RAWDATA372' AS RAWDATA372,                        "+
                    " 'RAWDATA373' AS RAWDATA373, 'RAWDATA374' AS RAWDATA374, 'RAWDATA375' AS RAWDATA375, 'RAWDATA376' AS RAWDATA376,                        "+
                    " 'RAWDATA377' AS RAWDATA377, 'RAWDATA378' AS RAWDATA378, 'RAWDATA379' AS RAWDATA379, 'RAWDATA380' AS RAWDATA380,                        "+
                    " 'RAWDATA381' AS RAWDATA381, 'RAWDATA382' AS RAWDATA382, 'RAWDATA383' AS RAWDATA383, 'RAWDATA384' AS RAWDATA384,                        "+
                    " 'RAWDATA385' AS RAWDATA385, 'RAWDATA386' AS RAWDATA386, 'RAWDATA387' AS RAWDATA387, 'RAWDATA388' AS RAWDATA388,                        "+
                    " 'RAWDATA389' AS RAWDATA389, 'RAWDATA390' AS RAWDATA390, 'RAWDATA391' AS RAWDATA391, 'RAWDATA392' AS RAWDATA392,                        "+
                    " 'RAWDATA393' AS RAWDATA393, 'RAWDATA394' AS RAWDATA394, 'RAWDATA395' AS RAWDATA395, 'RAWDATA396' AS RAWDATA396,                        "+
                    " 'RAWDATA397' AS RAWDATA397, 'RAWDATA398' AS RAWDATA398, 'RAWDATA399' AS RAWDATA399, 'RAWDATA400' AS RAWDATA400,                        "+
                    " 'RAWDATA401' AS RAWDATA401, 'RAWDATA402' AS RAWDATA402, 'RAWDATA403' AS RAWDATA403, 'RAWDATA404' AS RAWDATA404,                        "+
                    " 'RAWDATA405' AS RAWDATA405, 'RAWDATA406' AS RAWDATA406, 'RAWDATA407' AS RAWDATA407, 'RAWDATA408' AS RAWDATA408,                        "+
                    " 'RAWDATA409' AS RAWDATA409, 'RAWDATA410' AS RAWDATA410, 'RAWDATA411' AS RAWDATA411, 'RAWDATA412' AS RAWDATA412,                        "+
                    " 'RAWDATA413' AS RAWDATA413, 'RAWDATA414' AS RAWDATA414, 'RAWDATA415' AS RAWDATA415, 'RAWDATA416' AS RAWDATA416,                        "+
                    " 'RAWDATA417' AS RAWDATA417, 'RAWDATA418' AS RAWDATA418, 'RAWDATA419' AS RAWDATA419, 'RAWDATA420' AS RAWDATA420,                        "+
                    " 'RAWDATA421' AS RAWDATA421, 'RAWDATA422' AS RAWDATA422, 'RAWDATA423' AS RAWDATA423, 'RAWDATA424' AS RAWDATA424,                        "+
                    " 'RAWDATA425' AS RAWDATA425, 'RAWDATA426' AS RAWDATA426, 'RAWDATA427' AS RAWDATA427, 'RAWDATA428' AS RAWDATA428,                        "+
                    " 'RAWDATA429' AS RAWDATA429, 'RAWDATA430' AS RAWDATA430, 'RAWDATA431' AS RAWDATA431, 'RAWDATA432' AS RAWDATA432,                        "+
                    " 'RAWDATA433' AS RAWDATA433, 'RAWDATA434' AS RAWDATA434, 'RAWDATA435' AS RAWDATA435, 'RAWDATA436' AS RAWDATA436,                        "+
                    " 'RAWDATA437' AS RAWDATA437, 'RAWDATA438' AS RAWDATA438, 'RAWDATA439' AS RAWDATA439, 'RAWDATA440' AS RAWDATA440,                        "+
                    " 'RAWDATA441' AS RAWDATA441, 'RAWDATA442' AS RAWDATA442, 'RAWDATA443' AS RAWDATA443, 'RAWDATA444' AS RAWDATA444,                        "+
                    " 'RAWDATA445' AS RAWDATA445, 'RAWDATA446' AS RAWDATA446, 'RAWDATA447' AS RAWDATA447, 'RAWDATA448' AS RAWDATA448,                        "+
                    " 'RAWDATA449' AS RAWDATA449, 'RAWDATA450' AS RAWDATA450, 'RAWDATA451' AS RAWDATA451, 'RAWDATA452' AS RAWDATA452,                        "+
                    " 'RAWDATA453' AS RAWDATA453, 'RAWDATA454' AS RAWDATA454, 'RAWDATA455' AS RAWDATA455, 'RAWDATA456' AS RAWDATA456,                        "+
                    " 'RAWDATA457' AS RAWDATA457, 'RAWDATA458' AS RAWDATA458, 'RAWDATA459' AS RAWDATA459, 'RAWDATA460' AS RAWDATA460,                        "+
                    " 'RAWDATA461' AS RAWDATA461, 'RAWDATA462' AS RAWDATA462, 'RAWDATA463' AS RAWDATA463, 'RAWDATA464' AS RAWDATA464,                        "+
                    " 'RAWDATA465' AS RAWDATA465, 'RAWDATA466' AS RAWDATA466, 'RAWDATA467' AS RAWDATA467, 'RAWDATA468' AS RAWDATA468,                        "+
                    " 'RAWDATA469' AS RAWDATA469, 'RAWDATA470' AS RAWDATA470, 'RAWDATA471' AS RAWDATA471, 'RAWDATA472' AS RAWDATA472,                        "+
                    " 'RAWDATA473' AS RAWDATA473, 'RAWDATA474' AS RAWDATA474, 'RAWDATA475' AS RAWDATA475, 'RAWDATA476' AS RAWDATA476,                        "+
                    " 'RAWDATA477' AS RAWDATA477, 'RAWDATA478' AS RAWDATA478, 'RAWDATA479' AS RAWDATA479, 'RAWDATA480' AS RAWDATA480,                        "+
                    " 'RAWDATA481' AS RAWDATA481, 'RAWDATA482' AS RAWDATA482, 'RAWDATA483' AS RAWDATA483, 'RAWDATA484' AS RAWDATA484,                        "+
                    " 'RAWDATA485' AS RAWDATA485, 'RAWDATA486' AS RAWDATA486, 'RAWDATA487' AS RAWDATA487, 'RAWDATA488' AS RAWDATA488,                        "+
                    " 'RAWDATA489' AS RAWDATA489, 'RAWDATA490' AS RAWDATA490, 'RAWDATA491' AS RAWDATA491, 'RAWDATA492' AS RAWDATA492,                        "+
                    " 'RAWDATA493' AS RAWDATA493, 'RAWDATA494' AS RAWDATA494, 'RAWDATA495' AS RAWDATA495, 'RAWDATA496' AS RAWDATA496,                        "+
                    " 'RAWDATA497' AS RAWDATA497, 'RAWDATA498' AS RAWDATA498, 'RAWDATA499' AS RAWDATA499, 'RAWDATA500' AS RAWDATA500,                        "+
                    " 'RAWDATA501' AS RAWDATA501, 'RAWDATA502' AS RAWDATA502, 'RAWDATA503' AS RAWDATA503, 'RAWDATA504' AS RAWDATA504,                        "+
                    " 'RAWDATA505' AS RAWDATA505, 'RAWDATA506' AS RAWDATA506, 'RAWDATA507' AS RAWDATA507, 'RAWDATA508' AS RAWDATA508,                        "+
                    " 'RAWDATA509' AS RAWDATA509, 'RAWDATA510' AS RAWDATA510, 'RAWDATA511' AS RAWDATA511, 'RAWDATA512' AS RAWDATA512,                        "+
                    " 'RAWDATA513' AS RAWDATA513, 'RAWDATA514' AS RAWDATA514, 'RAWDATA515' AS RAWDATA515, 'RAWDATA516' AS RAWDATA516,                        "+
                    " 'RAWDATA517' AS RAWDATA517, 'RAWDATA518' AS RAWDATA518, 'RAWDATA519' AS RAWDATA519, 'RAWDATA520' AS RAWDATA520,                        "+
                    " 'RAWDATA521' AS RAWDATA521, 'RAWDATA522' AS RAWDATA522, 'RAWDATA523' AS RAWDATA523, 'RAWDATA524' AS RAWDATA524,                        "+
                    " 'RAWDATA525' AS RAWDATA525, 'RAWDATA526' AS RAWDATA526, 'RAWDATA527' AS RAWDATA527, 'RAWDATA528' AS RAWDATA528,                        "+
                    " 'RAWDATA529' AS RAWDATA529, 'RAWDATA530' AS RAWDATA530, 'RAWDATA531' AS RAWDATA531, 'RAWDATA532' AS RAWDATA532,                        "+
                    " 'RAWDATA533' AS RAWDATA533, 'RAWDATA534' AS RAWDATA534, 'RAWDATA535' AS RAWDATA535, 'RAWDATA536' AS RAWDATA536,                        "+
                    " 'RAWDATA537' AS RAWDATA537, 'RAWDATA538' AS RAWDATA538, 'RAWDATA539' AS RAWDATA539, 'RAWDATA540' AS RAWDATA540,                        "+
                    " 'RAWDATA541' AS RAWDATA541, 'RAWDATA542' AS RAWDATA542, 'RAWDATA543' AS RAWDATA543, 'RAWDATA544' AS RAWDATA544,                        "+
                    " 'RAWDATA545' AS RAWDATA545, 'RAWDATA546' AS RAWDATA546, 'RAWDATA547' AS RAWDATA547, 'RAWDATA548' AS RAWDATA548,                        "+
                    " 'RAWDATA549' AS RAWDATA549, 'RAWDATA550' AS RAWDATA550, 'RAWDATA551' AS RAWDATA551, 'RAWDATA552' AS RAWDATA552,                        "+
                    " 'RAWDATA553' AS RAWDATA553, 'RAWDATA554' AS RAWDATA554, 'RAWDATA555' AS RAWDATA555, 'RAWDATA556' AS RAWDATA556,                        "+
                    " 'RAWDATA557' AS RAWDATA557, 'RAWDATA558' AS RAWDATA558, 'RAWDATA559' AS RAWDATA559, 'RAWDATA560' AS RAWDATA560,                        "+
                    " 'RAWDATA561' AS RAWDATA561, 'RAWDATA562' AS RAWDATA562, 'RAWDATA563' AS RAWDATA563, 'RAWDATA564' AS RAWDATA564,                        "+
                    " 'RAWDATA565' AS RAWDATA565, 'RAWDATA566' AS RAWDATA566, 'RAWDATA567' AS RAWDATA567, 'RAWDATA568' AS RAWDATA568,                        "+
                    " 'RAWDATA569' AS RAWDATA569, 'RAWDATA570' AS RAWDATA570, 'RAWDATA571' AS RAWDATA571, 'RAWDATA572' AS RAWDATA572,                        "+
                    " 'RAWDATA573' AS RAWDATA573, 'RAWDATA574' AS RAWDATA574, 'RAWDATA575' AS RAWDATA575, 'RAWDATA576' AS RAWDATA576,                        "+
                    " 'RAWDATA577' AS RAWDATA577, 'RAWDATA578' AS RAWDATA578, 'RAWDATA579' AS RAWDATA579, 'RAWDATA580' AS RAWDATA580,                        "+
                    " 'RAWDATA581' AS RAWDATA581, 'RAWDATA582' AS RAWDATA582, 'RAWDATA583' AS RAWDATA583, 'RAWDATA584' AS RAWDATA584,                        "+
                    " 'RAWDATA585' AS RAWDATA585, 'RAWDATA586' AS RAWDATA586, 'RAWDATA587' AS RAWDATA587, 'RAWDATA588' AS RAWDATA588,                        "+
                    " 'RAWDATA589' AS RAWDATA589, 'RAWDATA590' AS RAWDATA590, 'RAWDATA591' AS RAWDATA591, 'RAWDATA592' AS RAWDATA592,                        "+
                    " 'RAWDATA593' AS RAWDATA593, 'RAWDATA594' AS RAWDATA594, 'RAWDATA595' AS RAWDATA595, 'RAWDATA596' AS RAWDATA596,                        "+
                    " 'RAWDATA597' AS RAWDATA597, 'RAWDATA598' AS RAWDATA598, 'RAWDATA599' AS RAWDATA599, 'RAWDATA600' AS RAWDATA600 )                       "+
                    " )    ORDER BY CHARTID,CONTROLDATASEQ                                                                                                        ";

//            long startTime=System.currentTimeMillis();
            list=ora_spc.query(sql,new BeanListHandler<ChartData>(ChartData.class));
//            long endTime=System.currentTimeMillis();
//            logger.info("获取 chartData success count :{}----time:{} ms",list.size(),(endTime-startTime));
            return list;
        }catch (Exception e){
            logger.error("获取 chartData error :{}----sql:{}",e.getMessage(),str.toString());
            throw new Exception("获取 chartData Exception:"+ e.getMessage());
        }


    }
    public List<ChartSpec> getChartSpecByChartId(EtlConfD etlConfD, String chartId, String specType)throws Exception{
  //      logger.info("执行getChartSpecByChartId chartId:{},specType:{}",chartId,specType);
        List<ChartSpec> list =new ArrayList<>();
        String sql ="";
        StringBuffer str =new StringBuffer();
//        String panelInCondition = set2InCondition(charIdList,"CD.CHARTID");
        try{
            sql+=" SELECT ROW_NUMBER() OVER(ORDER BY CD.CONTROLDATASEQ) AS SEQ,CD.CHARTID, CD.CONTROLDATASEQ, DD.MATERIALNAME, DD.DCDATAID,                          "+
                    " TO_CHAR(DD.PROCESSTIME, 'YYYY-MM-DD HH24:MI:SS') AS PROCESSTIME, CASE WHEN MD.MARKINGTIME IS NULL THEN 'N' ELSE                        "+
                    " 'Y' END AS MARKINGFLAG, CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END AS RESULT, LISTAGG(ADS.ALARMSEQ, ',')                "+
                    " WITHIN GROUP (ORDER BY ADS.ALARMSEQ) AS OOSALARMID, LISTAGG(ADC.ALARMSEQ, ',') WITHIN GROUP (ORDER BY ADC.ALARMSEQ)                    "+
                    " AS OOCALARMID, ADR.ALARMSEQ AS OORALARMID, CDS.TARGET, CDS.UPPERSPECLIMIT, CDS.LOWERSPECLIMIT, CDS.CENTERLINE,                         "+
                    " CDS.UPPERCONTROLLIMIT, CDS.LOWERCONTROLLIMIT, CASE WHEN MD.MARKINGTIME IS NOT NULL THEN CASE WHEN CDR.RESULT = 'NA'                    "+
                    " THEN NULL ELSE CDR.RESULT END WHEN MAX(ADS.ALARMSEQ) > 0 THEN CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END                "+
                    " WHEN MAX(ADC.ALARMSEQ) > 0 THEN CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END WHEN ADR.ALARMSEQ > 0 THEN                   "+
                    " CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END ELSE NULL END AS ALARMPOINT,                           "+
                    " CASE WHEN MD.MARKINGTIME IS NOT NULL THEN 'MAKING' WHEN MAX(ADS.ALARMSEQ) > 0 THEN 'OOS' WHEN MAX(ADC.ALARMSEQ) > 0                    "+
                    " THEN 'OOC' WHEN ADR.ALARMSEQ > 0 THEN 'OOR' ELSE NULL END AS ALARMTYPE FROM (SELECT /*+ INDEX(CD CONTROLDATA_IDX1) */ ROWNUM AS DATASEQ, "+
                    " CD.CHARTID, CD.CONTROLDATASEQ, CD.DCDATAID FROM CONTROLDATA CD       where 1=1                                                                   ";

//            str.append(" and CD.CHARTID = '495A1_A1870_WERP01_CD1_LOSS'  ");
           str.append(" and CD.CHARTID = '"+chartId+"'  ");
 //           str.append(" and CD.TIMEKEY >= '201806042350' AND CD.TIMEKEY < '201806050001' ");
            str.append(" and  CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())
                    +"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
            sql+=str.toString();
            sql+=" ) CD INNER JOIN DCDATA DD ON DD.DCDATAID = CD.DCDATAID INNER JOIN ENUMDEFVALUE EDV ON EDV.ENUMNAME = 'XbarS'      ";
            sql+=" AND EDV.ENUMVALUE = '"+specType+"' ";
            sql+=" LEFT JOIN CONTROLDATARESULT CDR ON CDR.CHARTID = CD.CHARTID AND CDR.CONTROLDATASEQ = CD.CONTROLDATASEQ          "+
                    " AND CDR.SITENAME = EDV.UDF1 LEFT JOIN MARKINGDATA MD ON MD.CHARTID = CD.CHARTID AND MD.CONTROLDATASEQ = CD.CONTROLDATASEQ              "+
                    " LEFT JOIN ALARMDATA ADS ON ADS.CHARTID = CD.CHARTID AND ADS.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADS.CHARTNAME = EDV.ENUMVALUE       "+
                    " AND ADS.ALARMTYPE = 'OOS' LEFT JOIN ALARMDATA ADC ON ADC.CHARTID = CD.CHARTID AND ADC.CONTROLDATASEQ = CD.CONTROLDATASEQ AND           "+
                    " ADC.CHARTNAME = EDV.ENUMVALUE AND 1 = CASE WHEN EDV.ENUMVALUE = 'Xbar' THEN CASE WHEN ADC.CHARTNAME IN ('Xbar', 'Raw')                 "+
                    " THEN 1 ELSE 0 END ELSE CASE WHEN EDV.ENUMVALUE = ADC.CHARTNAME THEN 1 ELSE 0 END END AND ADC.ALARMTYPE = 'OOC'                         "+
                    " LEFT JOIN ALARMDATA ADR ON ADR.CHARTID = CD.CHARTID AND ADR.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADR.CHARTNAME = EDV.ENUMVALUE       "+
                    " AND 1 = CASE WHEN EDV.ENUMVALUE = 'Xbar' THEN CASE WHEN ADR.CHARTNAME IN ('Xbar', 'Raw') THEN 1 ELSE 0 END ELSE                        "+
                    " CASE WHEN EDV.ENUMVALUE = ADR.CHARTNAME THEN 1 ELSE 0 END END AND ADR.ALARMTYPE = 'OOR' LEFT JOIN CONTROLDATASPEC CDS ON               "+
                    " CDS.CHARTID = CD.CHARTID AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ AND CDS.CHARTNAME = EDV.ENUMVALUE WHERE 1 = 1                      "+
                    " GROUP BY CD.CHARTID,CD.CONTROLDATASEQ, DD.MATERIALNAME, DD.DCDATAID, DD.PROCESSTIME, MD.MARKINGTIME , CDS.TARGET, CDS.UPPERSPECLIMIT,             "+
                    " CDS.LOWERSPECLIMIT, CDS.CENTERLINE, CDS.UPPERCONTROLLIMIT, CDS.LOWERCONTROLLIMIT , CDR.RESULT, ADR.ALARMSEQ                            "+
                    " ORDER BY CD.CHARTID,SEQ  ";
//            long startTime=System.currentTimeMillis();
            list=ora_spc.query(sql,new BeanListHandler<ChartSpec>(ChartSpec.class));
//            long endTime=System.currentTimeMillis();
 //           logger.info("获取 ChartSpec success specType:{}----count :{}----time:{} ms",specType,list.size(),(endTime-startTime));
            return list;
        }catch (Exception e){
            logger.error("获取 ChartSpec error :specType:{}----{}----sql:{}",specType,e.getMessage(),str.toString());
            throw new Exception("获取 ChartSpec Exception:"+ e.getMessage());
        }

    }

    public static String set2InCondition(List<String> mySet, String column) {
 //       logger.info("执行set2InCondition column:{}",column);
        StringBuffer inSb = new StringBuffer();

        if (!mySet.isEmpty()) {
            if(mySet.size()<1000){
                mySet.forEach(s -> inSb.append("'").append(s).append("',"));
            }else{
                List<String> list = new ArrayList<String>();
                inSb.append(column+" in(");
                list.addAll(mySet);
                for(int i=0;i<list.size();i++){
                    inSb.append("'").append(list.get(i)).append("',");
                    if(i>=500 && i % 500 == 0 && i!=list.size()-1){
                        inSb.deleteCharAt(inSb.length()-1);
                        inSb.append(") or  ").append(column+" in(");
                    }
                    if(i == list.size()-1){
                        inSb.deleteCharAt(inSb.length()-1);
                        inSb.append(" )  ");

                    }

                }

            }

            return inSb.substring(0, inSb.length() - 1);
        } else {
            return "";
        }
    }
    public static String rowDataToString(ChartData chartData){
 //       logger.info("执行rowDataToString chartData.getSiteCount():{}",chartData.getSiteCount());
        String rowDatas="";
        int count =chartData.getSiteCount();
        if(count==0) return rowDatas;
        rowDatas+=(chartData.getRawData001()!=null?chartData.getRawData001():"null")+",";
        rowDatas+=(chartData.getRawData002()!=null?chartData.getRawData002():"null")+",";
        rowDatas+=(chartData.getRawData003()!=null?chartData.getRawData003():"null")+",";
        rowDatas+=(chartData.getRawData004()!=null?chartData.getRawData004():"null")+",";
        rowDatas+=(chartData.getRawData005()!=null?chartData.getRawData005():"null")+",";
        rowDatas+=(chartData.getRawData006()!=null?chartData.getRawData006():"null")+",";
        rowDatas+=(chartData.getRawData007()!=null?chartData.getRawData007():"null")+",";
        rowDatas+=(chartData.getRawData008()!=null?chartData.getRawData008():"null")+",";
        rowDatas+=(chartData.getRawData009()!=null?chartData.getRawData009():"null")+",";
        rowDatas+=(chartData.getRawData010()!=null?chartData.getRawData010():"null")+",";
        rowDatas+=(chartData.getRawData011()!=null?chartData.getRawData011():"null")+",";
        rowDatas+=(chartData.getRawData012()!=null?chartData.getRawData012():"null")+",";
        rowDatas+=(chartData.getRawData013()!=null?chartData.getRawData013():"null")+",";
        rowDatas+=(chartData.getRawData014()!=null?chartData.getRawData014():"null")+",";
        rowDatas+=(chartData.getRawData015()!=null?chartData.getRawData015():"null")+",";
        rowDatas+=(chartData.getRawData016()!=null?chartData.getRawData016():"null")+",";
        rowDatas+=(chartData.getRawData017()!=null?chartData.getRawData017():"null")+",";
        rowDatas+=(chartData.getRawData018()!=null?chartData.getRawData018():"null")+",";
        rowDatas+=(chartData.getRawData019()!=null?chartData.getRawData019():"null")+",";
        rowDatas+=(chartData.getRawData020()!=null?chartData.getRawData020():"null")+",";
        rowDatas+=(chartData.getRawData021()!=null?chartData.getRawData021():"null")+",";
        rowDatas+=(chartData.getRawData022()!=null?chartData.getRawData022():"null")+",";
        rowDatas+=(chartData.getRawData023()!=null?chartData.getRawData023():"null")+",";
        rowDatas+=(chartData.getRawData024()!=null?chartData.getRawData024():"null")+",";
        rowDatas+=(chartData.getRawData025()!=null?chartData.getRawData025():"null")+",";
        rowDatas+=(chartData.getRawData026()!=null?chartData.getRawData026():"null")+",";
        rowDatas+=(chartData.getRawData027()!=null?chartData.getRawData027():"null")+",";
        rowDatas+=(chartData.getRawData028()!=null?chartData.getRawData028():"null")+",";
        rowDatas+=(chartData.getRawData029()!=null?chartData.getRawData029():"null")+",";
        rowDatas+=(chartData.getRawData030()!=null?chartData.getRawData030():"null")+",";
        rowDatas+=(chartData.getRawData031()!=null?chartData.getRawData031():"null")+",";
        rowDatas+=(chartData.getRawData032()!=null?chartData.getRawData032():"null")+",";
        rowDatas+=(chartData.getRawData033()!=null?chartData.getRawData033():"null")+",";
        rowDatas+=(chartData.getRawData034()!=null?chartData.getRawData034():"null")+",";
        rowDatas+=(chartData.getRawData035()!=null?chartData.getRawData035():"null")+",";
        rowDatas+=(chartData.getRawData036()!=null?chartData.getRawData036():"null")+",";
        rowDatas+=(chartData.getRawData037()!=null?chartData.getRawData037():"null")+",";
        rowDatas+=(chartData.getRawData038()!=null?chartData.getRawData038():"null")+",";
        rowDatas+=(chartData.getRawData039()!=null?chartData.getRawData039():"null")+",";
        rowDatas+=(chartData.getRawData040()!=null?chartData.getRawData040():"null")+",";
        rowDatas+=(chartData.getRawData041()!=null?chartData.getRawData041():"null")+",";
        rowDatas+=(chartData.getRawData042()!=null?chartData.getRawData042():"null")+",";
        rowDatas+=(chartData.getRawData043()!=null?chartData.getRawData043():"null")+",";
        rowDatas+=(chartData.getRawData044()!=null?chartData.getRawData044():"null")+",";
        rowDatas+=(chartData.getRawData045()!=null?chartData.getRawData045():"null")+",";
        rowDatas+=(chartData.getRawData046()!=null?chartData.getRawData046():"null")+",";
        rowDatas+=(chartData.getRawData047()!=null?chartData.getRawData047():"null")+",";
        rowDatas+=(chartData.getRawData048()!=null?chartData.getRawData048():"null")+",";
        rowDatas+=(chartData.getRawData049()!=null?chartData.getRawData049():"null")+",";
        rowDatas+=(chartData.getRawData050()!=null?chartData.getRawData050():"null")+",";
        rowDatas+=(chartData.getRawData051()!=null?chartData.getRawData051():"null")+",";
        rowDatas+=(chartData.getRawData052()!=null?chartData.getRawData052():"null")+",";
        rowDatas+=(chartData.getRawData053()!=null?chartData.getRawData053():"null")+",";
        rowDatas+=(chartData.getRawData054()!=null?chartData.getRawData054():"null")+",";
        rowDatas+=(chartData.getRawData055()!=null?chartData.getRawData055():"null")+",";
        rowDatas+=(chartData.getRawData056()!=null?chartData.getRawData056():"null")+",";
        rowDatas+=(chartData.getRawData057()!=null?chartData.getRawData057():"null")+",";
        rowDatas+=(chartData.getRawData058()!=null?chartData.getRawData058():"null")+",";
        rowDatas+=(chartData.getRawData059()!=null?chartData.getRawData059():"null")+",";
        rowDatas+=(chartData.getRawData060()!=null?chartData.getRawData060():"null")+",";
        rowDatas+=(chartData.getRawData061()!=null?chartData.getRawData061():"null")+",";
        rowDatas+=(chartData.getRawData062()!=null?chartData.getRawData062():"null")+",";
        rowDatas+=(chartData.getRawData063()!=null?chartData.getRawData063():"null")+",";
        rowDatas+=(chartData.getRawData064()!=null?chartData.getRawData064():"null")+",";
        rowDatas+=(chartData.getRawData065()!=null?chartData.getRawData065():"null")+",";
        rowDatas+=(chartData.getRawData066()!=null?chartData.getRawData066():"null")+",";
        rowDatas+=(chartData.getRawData067()!=null?chartData.getRawData067():"null")+",";
        rowDatas+=(chartData.getRawData068()!=null?chartData.getRawData068():"null")+",";
        rowDatas+=(chartData.getRawData069()!=null?chartData.getRawData069():"null")+",";
        rowDatas+=(chartData.getRawData070()!=null?chartData.getRawData070():"null")+",";
        rowDatas+=(chartData.getRawData071()!=null?chartData.getRawData071():"null")+",";
        rowDatas+=(chartData.getRawData072()!=null?chartData.getRawData072():"null")+",";
        rowDatas+=(chartData.getRawData073()!=null?chartData.getRawData073():"null")+",";
        rowDatas+=(chartData.getRawData074()!=null?chartData.getRawData074():"null")+",";
        rowDatas+=(chartData.getRawData075()!=null?chartData.getRawData075():"null")+",";
        rowDatas+=(chartData.getRawData076()!=null?chartData.getRawData076():"null")+",";
        rowDatas+=(chartData.getRawData077()!=null?chartData.getRawData077():"null")+",";
        rowDatas+=(chartData.getRawData078()!=null?chartData.getRawData078():"null")+",";
        rowDatas+=(chartData.getRawData079()!=null?chartData.getRawData079():"null")+",";
        rowDatas+=(chartData.getRawData080()!=null?chartData.getRawData080():"null")+",";
        rowDatas+=(chartData.getRawData081()!=null?chartData.getRawData081():"null")+",";
        rowDatas+=(chartData.getRawData082()!=null?chartData.getRawData082():"null")+",";
        rowDatas+=(chartData.getRawData083()!=null?chartData.getRawData083():"null")+",";
        rowDatas+=(chartData.getRawData084()!=null?chartData.getRawData084():"null")+",";
        rowDatas+=(chartData.getRawData085()!=null?chartData.getRawData085():"null")+",";
        rowDatas+=(chartData.getRawData086()!=null?chartData.getRawData086():"null")+",";
        rowDatas+=(chartData.getRawData087()!=null?chartData.getRawData087():"null")+",";
        rowDatas+=(chartData.getRawData088()!=null?chartData.getRawData088():"null")+",";
        rowDatas+=(chartData.getRawData089()!=null?chartData.getRawData089():"null")+",";
        rowDatas+=(chartData.getRawData090()!=null?chartData.getRawData090():"null")+",";
        rowDatas+=(chartData.getRawData091()!=null?chartData.getRawData091():"null")+",";
        rowDatas+=(chartData.getRawData092()!=null?chartData.getRawData092():"null")+",";
        rowDatas+=(chartData.getRawData093()!=null?chartData.getRawData093():"null")+",";
        rowDatas+=(chartData.getRawData094()!=null?chartData.getRawData094():"null")+",";
        rowDatas+=(chartData.getRawData095()!=null?chartData.getRawData095():"null")+",";
        rowDatas+=(chartData.getRawData096()!=null?chartData.getRawData096():"null")+",";
        rowDatas+=(chartData.getRawData097()!=null?chartData.getRawData097():"null")+",";
        rowDatas+=(chartData.getRawData098()!=null?chartData.getRawData098():"null")+",";
        rowDatas+=(chartData.getRawData099()!=null?chartData.getRawData099():"null")+",";
        rowDatas+=(chartData.getRawData100()!=null?chartData.getRawData100():"null")+",";
        rowDatas+=(chartData.getRawData101()!=null?chartData.getRawData101():"null")+",";
        rowDatas+=(chartData.getRawData102()!=null?chartData.getRawData102():"null")+",";
        rowDatas+=(chartData.getRawData103()!=null?chartData.getRawData103():"null")+",";
        rowDatas+=(chartData.getRawData104()!=null?chartData.getRawData104():"null")+",";
        rowDatas+=(chartData.getRawData105()!=null?chartData.getRawData105():"null")+",";
        rowDatas+=(chartData.getRawData106()!=null?chartData.getRawData106():"null")+",";
        rowDatas+=(chartData.getRawData107()!=null?chartData.getRawData107():"null")+",";
        rowDatas+=(chartData.getRawData108()!=null?chartData.getRawData108():"null")+",";
        rowDatas+=(chartData.getRawData109()!=null?chartData.getRawData109():"null")+",";
        rowDatas+=(chartData.getRawData110()!=null?chartData.getRawData110():"null")+",";
        rowDatas+=(chartData.getRawData111()!=null?chartData.getRawData111():"null")+",";
        rowDatas+=(chartData.getRawData112()!=null?chartData.getRawData112():"null")+",";
        rowDatas+=(chartData.getRawData113()!=null?chartData.getRawData113():"null")+",";
        rowDatas+=(chartData.getRawData114()!=null?chartData.getRawData114():"null")+",";
        rowDatas+=(chartData.getRawData115()!=null?chartData.getRawData115():"null")+",";
        rowDatas+=(chartData.getRawData116()!=null?chartData.getRawData116():"null")+",";
        rowDatas+=(chartData.getRawData117()!=null?chartData.getRawData117():"null")+",";
        rowDatas+=(chartData.getRawData118()!=null?chartData.getRawData118():"null")+",";
        rowDatas+=(chartData.getRawData119()!=null?chartData.getRawData119():"null")+",";
        rowDatas+=(chartData.getRawData120()!=null?chartData.getRawData120():"null")+",";
        rowDatas+=(chartData.getRawData121()!=null?chartData.getRawData121():"null")+",";
        rowDatas+=(chartData.getRawData122()!=null?chartData.getRawData122():"null")+",";
        rowDatas+=(chartData.getRawData123()!=null?chartData.getRawData123():"null")+",";
        rowDatas+=(chartData.getRawData124()!=null?chartData.getRawData124():"null")+",";
        rowDatas+=(chartData.getRawData125()!=null?chartData.getRawData125():"null")+",";
        rowDatas+=(chartData.getRawData126()!=null?chartData.getRawData126():"null")+",";
        rowDatas+=(chartData.getRawData127()!=null?chartData.getRawData127():"null")+",";
        rowDatas+=(chartData.getRawData128()!=null?chartData.getRawData128():"null")+",";
        rowDatas+=(chartData.getRawData129()!=null?chartData.getRawData129():"null")+",";
        rowDatas+=(chartData.getRawData130()!=null?chartData.getRawData130():"null")+",";
        rowDatas+=(chartData.getRawData131()!=null?chartData.getRawData131():"null")+",";
        rowDatas+=(chartData.getRawData132()!=null?chartData.getRawData132():"null")+",";
        rowDatas+=(chartData.getRawData133()!=null?chartData.getRawData133():"null")+",";
        rowDatas+=(chartData.getRawData134()!=null?chartData.getRawData134():"null")+",";
        rowDatas+=(chartData.getRawData135()!=null?chartData.getRawData135():"null")+",";
        rowDatas+=(chartData.getRawData136()!=null?chartData.getRawData136():"null")+",";
        rowDatas+=(chartData.getRawData137()!=null?chartData.getRawData137():"null")+",";
        rowDatas+=(chartData.getRawData138()!=null?chartData.getRawData138():"null")+",";
        rowDatas+=(chartData.getRawData139()!=null?chartData.getRawData139():"null")+",";
        rowDatas+=(chartData.getRawData140()!=null?chartData.getRawData140():"null")+",";
        rowDatas+=(chartData.getRawData141()!=null?chartData.getRawData141():"null")+",";
        rowDatas+=(chartData.getRawData142()!=null?chartData.getRawData142():"null")+",";
        rowDatas+=(chartData.getRawData143()!=null?chartData.getRawData143():"null")+",";
        rowDatas+=(chartData.getRawData144()!=null?chartData.getRawData144():"null")+",";
        rowDatas+=(chartData.getRawData145()!=null?chartData.getRawData145():"null")+",";
        rowDatas+=(chartData.getRawData146()!=null?chartData.getRawData146():"null")+",";
        rowDatas+=(chartData.getRawData147()!=null?chartData.getRawData147():"null")+",";
        rowDatas+=(chartData.getRawData148()!=null?chartData.getRawData148():"null")+",";
        rowDatas+=(chartData.getRawData149()!=null?chartData.getRawData149():"null")+",";
        rowDatas+=(chartData.getRawData150()!=null?chartData.getRawData150():"null")+",";
        rowDatas+=(chartData.getRawData151()!=null?chartData.getRawData151():"null")+",";
        rowDatas+=(chartData.getRawData152()!=null?chartData.getRawData152():"null")+",";
        rowDatas+=(chartData.getRawData153()!=null?chartData.getRawData153():"null")+",";
        rowDatas+=(chartData.getRawData154()!=null?chartData.getRawData154():"null")+",";
        rowDatas+=(chartData.getRawData155()!=null?chartData.getRawData155():"null")+",";
        rowDatas+=(chartData.getRawData156()!=null?chartData.getRawData156():"null")+",";
        rowDatas+=(chartData.getRawData157()!=null?chartData.getRawData157():"null")+",";
        rowDatas+=(chartData.getRawData158()!=null?chartData.getRawData158():"null")+",";
        rowDatas+=(chartData.getRawData159()!=null?chartData.getRawData159():"null")+",";
        rowDatas+=(chartData.getRawData160()!=null?chartData.getRawData160():"null")+",";
        rowDatas+=(chartData.getRawData161()!=null?chartData.getRawData161():"null")+",";
        rowDatas+=(chartData.getRawData162()!=null?chartData.getRawData162():"null")+",";
        rowDatas+=(chartData.getRawData163()!=null?chartData.getRawData163():"null")+",";
        rowDatas+=(chartData.getRawData164()!=null?chartData.getRawData164():"null")+",";
        rowDatas+=(chartData.getRawData165()!=null?chartData.getRawData165():"null")+",";
        rowDatas+=(chartData.getRawData166()!=null?chartData.getRawData166():"null")+",";
        rowDatas+=(chartData.getRawData167()!=null?chartData.getRawData167():"null")+",";
        rowDatas+=(chartData.getRawData168()!=null?chartData.getRawData168():"null")+",";
        rowDatas+=(chartData.getRawData169()!=null?chartData.getRawData169():"null")+",";
        rowDatas+=(chartData.getRawData170()!=null?chartData.getRawData170():"null")+",";
        rowDatas+=(chartData.getRawData171()!=null?chartData.getRawData171():"null")+",";
        rowDatas+=(chartData.getRawData172()!=null?chartData.getRawData172():"null")+",";
        rowDatas+=(chartData.getRawData173()!=null?chartData.getRawData173():"null")+",";
        rowDatas+=(chartData.getRawData174()!=null?chartData.getRawData174():"null")+",";
        rowDatas+=(chartData.getRawData175()!=null?chartData.getRawData175():"null")+",";
        rowDatas+=(chartData.getRawData176()!=null?chartData.getRawData176():"null")+",";
        rowDatas+=(chartData.getRawData177()!=null?chartData.getRawData177():"null")+",";
        rowDatas+=(chartData.getRawData178()!=null?chartData.getRawData178():"null")+",";
        rowDatas+=(chartData.getRawData179()!=null?chartData.getRawData179():"null")+",";
        rowDatas+=(chartData.getRawData180()!=null?chartData.getRawData180():"null")+",";
        rowDatas+=(chartData.getRawData181()!=null?chartData.getRawData181():"null")+",";
        rowDatas+=(chartData.getRawData182()!=null?chartData.getRawData182():"null")+",";
        rowDatas+=(chartData.getRawData183()!=null?chartData.getRawData183():"null")+",";
        rowDatas+=(chartData.getRawData184()!=null?chartData.getRawData184():"null")+",";
        rowDatas+=(chartData.getRawData185()!=null?chartData.getRawData185():"null")+",";
        rowDatas+=(chartData.getRawData186()!=null?chartData.getRawData186():"null")+",";
        rowDatas+=(chartData.getRawData187()!=null?chartData.getRawData187():"null")+",";
        rowDatas+=(chartData.getRawData188()!=null?chartData.getRawData188():"null")+",";
        rowDatas+=(chartData.getRawData189()!=null?chartData.getRawData189():"null")+",";
        rowDatas+=(chartData.getRawData190()!=null?chartData.getRawData190():"null")+",";
        rowDatas+=(chartData.getRawData191()!=null?chartData.getRawData191():"null")+",";
        rowDatas+=(chartData.getRawData192()!=null?chartData.getRawData192():"null")+",";
        rowDatas+=(chartData.getRawData193()!=null?chartData.getRawData193():"null")+",";
        rowDatas+=(chartData.getRawData194()!=null?chartData.getRawData194():"null")+",";
        rowDatas+=(chartData.getRawData195()!=null?chartData.getRawData195():"null")+",";
        rowDatas+=(chartData.getRawData196()!=null?chartData.getRawData196():"null")+",";
        rowDatas+=(chartData.getRawData197()!=null?chartData.getRawData197():"null")+",";
        rowDatas+=(chartData.getRawData198()!=null?chartData.getRawData198():"null")+",";
        rowDatas+=(chartData.getRawData199()!=null?chartData.getRawData199():"null")+",";
        rowDatas+=(chartData.getRawData200()!=null?chartData.getRawData200():"null")+",";
        rowDatas+=(chartData.getRawData201()!=null?chartData.getRawData201():"null")+",";
        rowDatas+=(chartData.getRawData202()!=null?chartData.getRawData202():"null")+",";
        rowDatas+=(chartData.getRawData203()!=null?chartData.getRawData203():"null")+",";
        rowDatas+=(chartData.getRawData204()!=null?chartData.getRawData204():"null")+",";
        rowDatas+=(chartData.getRawData205()!=null?chartData.getRawData205():"null")+",";
        rowDatas+=(chartData.getRawData206()!=null?chartData.getRawData206():"null")+",";
        rowDatas+=(chartData.getRawData207()!=null?chartData.getRawData207():"null")+",";
        rowDatas+=(chartData.getRawData208()!=null?chartData.getRawData208():"null")+",";
        rowDatas+=(chartData.getRawData209()!=null?chartData.getRawData209():"null")+",";
        rowDatas+=(chartData.getRawData210()!=null?chartData.getRawData210():"null")+",";
        rowDatas+=(chartData.getRawData211()!=null?chartData.getRawData211():"null")+",";
        rowDatas+=(chartData.getRawData212()!=null?chartData.getRawData212():"null")+",";
        rowDatas+=(chartData.getRawData213()!=null?chartData.getRawData213():"null")+",";
        rowDatas+=(chartData.getRawData214()!=null?chartData.getRawData214():"null")+",";
        rowDatas+=(chartData.getRawData215()!=null?chartData.getRawData215():"null")+",";
        rowDatas+=(chartData.getRawData216()!=null?chartData.getRawData216():"null")+",";
        rowDatas+=(chartData.getRawData217()!=null?chartData.getRawData217():"null")+",";
        rowDatas+=(chartData.getRawData218()!=null?chartData.getRawData218():"null")+",";
        rowDatas+=(chartData.getRawData219()!=null?chartData.getRawData219():"null")+",";
        rowDatas+=(chartData.getRawData220()!=null?chartData.getRawData220():"null")+",";
        rowDatas+=(chartData.getRawData221()!=null?chartData.getRawData221():"null")+",";
        rowDatas+=(chartData.getRawData222()!=null?chartData.getRawData222():"null")+",";
        rowDatas+=(chartData.getRawData223()!=null?chartData.getRawData223():"null")+",";
        rowDatas+=(chartData.getRawData224()!=null?chartData.getRawData224():"null")+",";
        rowDatas+=(chartData.getRawData225()!=null?chartData.getRawData225():"null")+",";
        rowDatas+=(chartData.getRawData226()!=null?chartData.getRawData226():"null")+",";
        rowDatas+=(chartData.getRawData227()!=null?chartData.getRawData227():"null")+",";
        rowDatas+=(chartData.getRawData228()!=null?chartData.getRawData228():"null")+",";
        rowDatas+=(chartData.getRawData229()!=null?chartData.getRawData229():"null")+",";
        rowDatas+=(chartData.getRawData230()!=null?chartData.getRawData230():"null")+",";
        rowDatas+=(chartData.getRawData231()!=null?chartData.getRawData231():"null")+",";
        rowDatas+=(chartData.getRawData232()!=null?chartData.getRawData232():"null")+",";
        rowDatas+=(chartData.getRawData233()!=null?chartData.getRawData233():"null")+",";
        rowDatas+=(chartData.getRawData234()!=null?chartData.getRawData234():"null")+",";
        rowDatas+=(chartData.getRawData235()!=null?chartData.getRawData235():"null")+",";
        rowDatas+=(chartData.getRawData236()!=null?chartData.getRawData236():"null")+",";
        rowDatas+=(chartData.getRawData237()!=null?chartData.getRawData237():"null")+",";
        rowDatas+=(chartData.getRawData238()!=null?chartData.getRawData238():"null")+",";
        rowDatas+=(chartData.getRawData239()!=null?chartData.getRawData239():"null")+",";
        rowDatas+=(chartData.getRawData240()!=null?chartData.getRawData240():"null")+",";
        rowDatas+=(chartData.getRawData241()!=null?chartData.getRawData241():"null")+",";
        rowDatas+=(chartData.getRawData242()!=null?chartData.getRawData242():"null")+",";
        rowDatas+=(chartData.getRawData243()!=null?chartData.getRawData243():"null")+",";
        rowDatas+=(chartData.getRawData244()!=null?chartData.getRawData244():"null")+",";
        rowDatas+=(chartData.getRawData245()!=null?chartData.getRawData245():"null")+",";
        rowDatas+=(chartData.getRawData246()!=null?chartData.getRawData246():"null")+",";
        rowDatas+=(chartData.getRawData247()!=null?chartData.getRawData247():"null")+",";
        rowDatas+=(chartData.getRawData248()!=null?chartData.getRawData248():"null")+",";
        rowDatas+=(chartData.getRawData249()!=null?chartData.getRawData249():"null")+",";
        rowDatas+=(chartData.getRawData250()!=null?chartData.getRawData250():"null")+",";
        rowDatas+=(chartData.getRawData251()!=null?chartData.getRawData251():"null")+",";
        rowDatas+=(chartData.getRawData252()!=null?chartData.getRawData252():"null")+",";
        rowDatas+=(chartData.getRawData253()!=null?chartData.getRawData253():"null")+",";
        rowDatas+=(chartData.getRawData254()!=null?chartData.getRawData254():"null")+",";
        rowDatas+=(chartData.getRawData255()!=null?chartData.getRawData255():"null")+",";
        rowDatas+=(chartData.getRawData256()!=null?chartData.getRawData256():"null")+",";
        rowDatas+=(chartData.getRawData257()!=null?chartData.getRawData257():"null")+",";
        rowDatas+=(chartData.getRawData258()!=null?chartData.getRawData258():"null")+",";
        rowDatas+=(chartData.getRawData259()!=null?chartData.getRawData259():"null")+",";
        rowDatas+=(chartData.getRawData260()!=null?chartData.getRawData260():"null")+",";
        rowDatas+=(chartData.getRawData261()!=null?chartData.getRawData261():"null")+",";
        rowDatas+=(chartData.getRawData262()!=null?chartData.getRawData262():"null")+",";
        rowDatas+=(chartData.getRawData263()!=null?chartData.getRawData263():"null")+",";
        rowDatas+=(chartData.getRawData264()!=null?chartData.getRawData264():"null")+",";
        rowDatas+=(chartData.getRawData265()!=null?chartData.getRawData265():"null")+",";
        rowDatas+=(chartData.getRawData266()!=null?chartData.getRawData266():"null")+",";
        rowDatas+=(chartData.getRawData267()!=null?chartData.getRawData267():"null")+",";
        rowDatas+=(chartData.getRawData268()!=null?chartData.getRawData268():"null")+",";
        rowDatas+=(chartData.getRawData269()!=null?chartData.getRawData269():"null")+",";
        rowDatas+=(chartData.getRawData270()!=null?chartData.getRawData270():"null")+",";
        rowDatas+=(chartData.getRawData271()!=null?chartData.getRawData271():"null")+",";
        rowDatas+=(chartData.getRawData272()!=null?chartData.getRawData272():"null")+",";
        rowDatas+=(chartData.getRawData273()!=null?chartData.getRawData273():"null")+",";
        rowDatas+=(chartData.getRawData274()!=null?chartData.getRawData274():"null")+",";
        rowDatas+=(chartData.getRawData275()!=null?chartData.getRawData275():"null")+",";
        rowDatas+=(chartData.getRawData276()!=null?chartData.getRawData276():"null")+",";
        rowDatas+=(chartData.getRawData277()!=null?chartData.getRawData277():"null")+",";
        rowDatas+=(chartData.getRawData278()!=null?chartData.getRawData278():"null")+",";
        rowDatas+=(chartData.getRawData279()!=null?chartData.getRawData279():"null")+",";
        rowDatas+=(chartData.getRawData280()!=null?chartData.getRawData280():"null")+",";
        rowDatas+=(chartData.getRawData281()!=null?chartData.getRawData281():"null")+",";
        rowDatas+=(chartData.getRawData282()!=null?chartData.getRawData282():"null")+",";
        rowDatas+=(chartData.getRawData283()!=null?chartData.getRawData283():"null")+",";
        rowDatas+=(chartData.getRawData284()!=null?chartData.getRawData284():"null")+",";
        rowDatas+=(chartData.getRawData285()!=null?chartData.getRawData285():"null")+",";
        rowDatas+=(chartData.getRawData286()!=null?chartData.getRawData286():"null")+",";
        rowDatas+=(chartData.getRawData287()!=null?chartData.getRawData287():"null")+",";
        rowDatas+=(chartData.getRawData288()!=null?chartData.getRawData288():"null")+",";
        rowDatas+=(chartData.getRawData289()!=null?chartData.getRawData289():"null")+",";
        rowDatas+=(chartData.getRawData290()!=null?chartData.getRawData290():"null")+",";
        rowDatas+=(chartData.getRawData291()!=null?chartData.getRawData291():"null")+",";
        rowDatas+=(chartData.getRawData292()!=null?chartData.getRawData292():"null")+",";
        rowDatas+=(chartData.getRawData293()!=null?chartData.getRawData293():"null")+",";
        rowDatas+=(chartData.getRawData294()!=null?chartData.getRawData294():"null")+",";
        rowDatas+=(chartData.getRawData295()!=null?chartData.getRawData295():"null")+",";
        rowDatas+=(chartData.getRawData296()!=null?chartData.getRawData296():"null")+",";
        rowDatas+=(chartData.getRawData297()!=null?chartData.getRawData297():"null")+",";
        rowDatas+=(chartData.getRawData298()!=null?chartData.getRawData298():"null")+",";
        rowDatas+=(chartData.getRawData299()!=null?chartData.getRawData299():"null")+",";
        rowDatas+=(chartData.getRawData300()!=null?chartData.getRawData300():"null")+",";
        rowDatas+=(chartData.getRawData301()!=null?chartData.getRawData301():"null")+",";
        rowDatas+=(chartData.getRawData302()!=null?chartData.getRawData302():"null")+",";
        rowDatas+=(chartData.getRawData303()!=null?chartData.getRawData303():"null")+",";
        rowDatas+=(chartData.getRawData304()!=null?chartData.getRawData304():"null")+",";
        rowDatas+=(chartData.getRawData305()!=null?chartData.getRawData305():"null")+",";
        rowDatas+=(chartData.getRawData306()!=null?chartData.getRawData306():"null")+",";
        rowDatas+=(chartData.getRawData307()!=null?chartData.getRawData307():"null")+",";
        rowDatas+=(chartData.getRawData308()!=null?chartData.getRawData308():"null")+",";
        rowDatas+=(chartData.getRawData309()!=null?chartData.getRawData309():"null")+",";
        rowDatas+=(chartData.getRawData310()!=null?chartData.getRawData310():"null")+",";
        rowDatas+=(chartData.getRawData311()!=null?chartData.getRawData311():"null")+",";
        rowDatas+=(chartData.getRawData312()!=null?chartData.getRawData312():"null")+",";
        rowDatas+=(chartData.getRawData313()!=null?chartData.getRawData313():"null")+",";
        rowDatas+=(chartData.getRawData314()!=null?chartData.getRawData314():"null")+",";
        rowDatas+=(chartData.getRawData315()!=null?chartData.getRawData315():"null")+",";
        rowDatas+=(chartData.getRawData316()!=null?chartData.getRawData316():"null")+",";
        rowDatas+=(chartData.getRawData317()!=null?chartData.getRawData317():"null")+",";
        rowDatas+=(chartData.getRawData318()!=null?chartData.getRawData318():"null")+",";
        rowDatas+=(chartData.getRawData319()!=null?chartData.getRawData319():"null")+",";
        rowDatas+=(chartData.getRawData320()!=null?chartData.getRawData320():"null")+",";
        rowDatas+=(chartData.getRawData321()!=null?chartData.getRawData321():"null")+",";
        rowDatas+=(chartData.getRawData322()!=null?chartData.getRawData322():"null")+",";
        rowDatas+=(chartData.getRawData323()!=null?chartData.getRawData323():"null")+",";
        rowDatas+=(chartData.getRawData324()!=null?chartData.getRawData324():"null")+",";
        rowDatas+=(chartData.getRawData325()!=null?chartData.getRawData325():"null")+",";
        rowDatas+=(chartData.getRawData326()!=null?chartData.getRawData326():"null")+",";
        rowDatas+=(chartData.getRawData327()!=null?chartData.getRawData327():"null")+",";
        rowDatas+=(chartData.getRawData328()!=null?chartData.getRawData328():"null")+",";
        rowDatas+=(chartData.getRawData329()!=null?chartData.getRawData329():"null")+",";
        rowDatas+=(chartData.getRawData330()!=null?chartData.getRawData330():"null")+",";
        rowDatas+=(chartData.getRawData331()!=null?chartData.getRawData331():"null")+",";
        rowDatas+=(chartData.getRawData332()!=null?chartData.getRawData332():"null")+",";
        rowDatas+=(chartData.getRawData333()!=null?chartData.getRawData333():"null")+",";
        rowDatas+=(chartData.getRawData334()!=null?chartData.getRawData334():"null")+",";
        rowDatas+=(chartData.getRawData335()!=null?chartData.getRawData335():"null")+",";
        rowDatas+=(chartData.getRawData336()!=null?chartData.getRawData336():"null")+",";
        rowDatas+=(chartData.getRawData337()!=null?chartData.getRawData337():"null")+",";
        rowDatas+=(chartData.getRawData338()!=null?chartData.getRawData338():"null")+",";
        rowDatas+=(chartData.getRawData339()!=null?chartData.getRawData339():"null")+",";
        rowDatas+=(chartData.getRawData340()!=null?chartData.getRawData340():"null")+",";
        rowDatas+=(chartData.getRawData341()!=null?chartData.getRawData341():"null")+",";
        rowDatas+=(chartData.getRawData342()!=null?chartData.getRawData342():"null")+",";
        rowDatas+=(chartData.getRawData343()!=null?chartData.getRawData343():"null")+",";
        rowDatas+=(chartData.getRawData344()!=null?chartData.getRawData344():"null")+",";
        rowDatas+=(chartData.getRawData345()!=null?chartData.getRawData345():"null")+",";
        rowDatas+=(chartData.getRawData346()!=null?chartData.getRawData346():"null")+",";
        rowDatas+=(chartData.getRawData347()!=null?chartData.getRawData347():"null")+",";
        rowDatas+=(chartData.getRawData348()!=null?chartData.getRawData348():"null")+",";
        rowDatas+=(chartData.getRawData349()!=null?chartData.getRawData349():"null")+",";
        rowDatas+=(chartData.getRawData350()!=null?chartData.getRawData350():"null")+",";
        rowDatas+=(chartData.getRawData351()!=null?chartData.getRawData351():"null")+",";
        rowDatas+=(chartData.getRawData352()!=null?chartData.getRawData352():"null")+",";
        rowDatas+=(chartData.getRawData353()!=null?chartData.getRawData353():"null")+",";
        rowDatas+=(chartData.getRawData354()!=null?chartData.getRawData354():"null")+",";
        rowDatas+=(chartData.getRawData355()!=null?chartData.getRawData355():"null")+",";
        rowDatas+=(chartData.getRawData356()!=null?chartData.getRawData356():"null")+",";
        rowDatas+=(chartData.getRawData357()!=null?chartData.getRawData357():"null")+",";
        rowDatas+=(chartData.getRawData358()!=null?chartData.getRawData358():"null")+",";
        rowDatas+=(chartData.getRawData359()!=null?chartData.getRawData359():"null")+",";
        rowDatas+=(chartData.getRawData360()!=null?chartData.getRawData360():"null")+",";
        rowDatas+=(chartData.getRawData361()!=null?chartData.getRawData361():"null")+",";
        rowDatas+=(chartData.getRawData362()!=null?chartData.getRawData362():"null")+",";
        rowDatas+=(chartData.getRawData363()!=null?chartData.getRawData363():"null")+",";
        rowDatas+=(chartData.getRawData364()!=null?chartData.getRawData364():"null")+",";
        rowDatas+=(chartData.getRawData365()!=null?chartData.getRawData365():"null")+",";
        rowDatas+=(chartData.getRawData366()!=null?chartData.getRawData366():"null")+",";
        rowDatas+=(chartData.getRawData367()!=null?chartData.getRawData367():"null")+",";
        rowDatas+=(chartData.getRawData368()!=null?chartData.getRawData368():"null")+",";
        rowDatas+=(chartData.getRawData369()!=null?chartData.getRawData369():"null")+",";
        rowDatas+=(chartData.getRawData370()!=null?chartData.getRawData370():"null")+",";
        rowDatas+=(chartData.getRawData371()!=null?chartData.getRawData371():"null")+",";
        rowDatas+=(chartData.getRawData372()!=null?chartData.getRawData372():"null")+",";
        rowDatas+=(chartData.getRawData373()!=null?chartData.getRawData373():"null")+",";
        rowDatas+=(chartData.getRawData374()!=null?chartData.getRawData374():"null")+",";
        rowDatas+=(chartData.getRawData375()!=null?chartData.getRawData375():"null")+",";
        rowDatas+=(chartData.getRawData376()!=null?chartData.getRawData376():"null")+",";
        rowDatas+=(chartData.getRawData377()!=null?chartData.getRawData377():"null")+",";
        rowDatas+=(chartData.getRawData378()!=null?chartData.getRawData378():"null")+",";
        rowDatas+=(chartData.getRawData379()!=null?chartData.getRawData379():"null")+",";
        rowDatas+=(chartData.getRawData380()!=null?chartData.getRawData380():"null")+",";
        rowDatas+=(chartData.getRawData381()!=null?chartData.getRawData381():"null")+",";
        rowDatas+=(chartData.getRawData382()!=null?chartData.getRawData382():"null")+",";
        rowDatas+=(chartData.getRawData383()!=null?chartData.getRawData383():"null")+",";
        rowDatas+=(chartData.getRawData384()!=null?chartData.getRawData384():"null")+",";
        rowDatas+=(chartData.getRawData385()!=null?chartData.getRawData385():"null")+",";
        rowDatas+=(chartData.getRawData386()!=null?chartData.getRawData386():"null")+",";
        rowDatas+=(chartData.getRawData387()!=null?chartData.getRawData387():"null")+",";
        rowDatas+=(chartData.getRawData388()!=null?chartData.getRawData388():"null")+",";
        rowDatas+=(chartData.getRawData389()!=null?chartData.getRawData389():"null")+",";
        rowDatas+=(chartData.getRawData390()!=null?chartData.getRawData390():"null")+",";
        rowDatas+=(chartData.getRawData391()!=null?chartData.getRawData391():"null")+",";
        rowDatas+=(chartData.getRawData392()!=null?chartData.getRawData392():"null")+",";
        rowDatas+=(chartData.getRawData393()!=null?chartData.getRawData393():"null")+",";
        rowDatas+=(chartData.getRawData394()!=null?chartData.getRawData394():"null")+",";
        rowDatas+=(chartData.getRawData395()!=null?chartData.getRawData395():"null")+",";
        rowDatas+=(chartData.getRawData396()!=null?chartData.getRawData396():"null")+",";
        rowDatas+=(chartData.getRawData397()!=null?chartData.getRawData397():"null")+",";
        rowDatas+=(chartData.getRawData398()!=null?chartData.getRawData398():"null")+",";
        rowDatas+=(chartData.getRawData399()!=null?chartData.getRawData399():"null")+",";
        rowDatas+=(chartData.getRawData400()!=null?chartData.getRawData400():"null")+",";
        rowDatas+=(chartData.getRawData401()!=null?chartData.getRawData401():"null")+",";
        rowDatas+=(chartData.getRawData402()!=null?chartData.getRawData402():"null")+",";
        rowDatas+=(chartData.getRawData403()!=null?chartData.getRawData403():"null")+",";
        rowDatas+=(chartData.getRawData404()!=null?chartData.getRawData404():"null")+",";
        rowDatas+=(chartData.getRawData405()!=null?chartData.getRawData405():"null")+",";
        rowDatas+=(chartData.getRawData406()!=null?chartData.getRawData406():"null")+",";
        rowDatas+=(chartData.getRawData407()!=null?chartData.getRawData407():"null")+",";
        rowDatas+=(chartData.getRawData408()!=null?chartData.getRawData408():"null")+",";
        rowDatas+=(chartData.getRawData409()!=null?chartData.getRawData409():"null")+",";
        rowDatas+=(chartData.getRawData410()!=null?chartData.getRawData410():"null")+",";
        rowDatas+=(chartData.getRawData411()!=null?chartData.getRawData411():"null")+",";
        rowDatas+=(chartData.getRawData412()!=null?chartData.getRawData412():"null")+",";
        rowDatas+=(chartData.getRawData413()!=null?chartData.getRawData413():"null")+",";
        rowDatas+=(chartData.getRawData414()!=null?chartData.getRawData414():"null")+",";
        rowDatas+=(chartData.getRawData415()!=null?chartData.getRawData415():"null")+",";
        rowDatas+=(chartData.getRawData416()!=null?chartData.getRawData416():"null")+",";
        rowDatas+=(chartData.getRawData417()!=null?chartData.getRawData417():"null")+",";
        rowDatas+=(chartData.getRawData418()!=null?chartData.getRawData418():"null")+",";
        rowDatas+=(chartData.getRawData419()!=null?chartData.getRawData419():"null")+",";
        rowDatas+=(chartData.getRawData420()!=null?chartData.getRawData420():"null")+",";
        rowDatas+=(chartData.getRawData421()!=null?chartData.getRawData421():"null")+",";
        rowDatas+=(chartData.getRawData422()!=null?chartData.getRawData422():"null")+",";
        rowDatas+=(chartData.getRawData423()!=null?chartData.getRawData423():"null")+",";
        rowDatas+=(chartData.getRawData424()!=null?chartData.getRawData424():"null")+",";
        rowDatas+=(chartData.getRawData425()!=null?chartData.getRawData425():"null")+",";
        rowDatas+=(chartData.getRawData426()!=null?chartData.getRawData426():"null")+",";
        rowDatas+=(chartData.getRawData427()!=null?chartData.getRawData427():"null")+",";
        rowDatas+=(chartData.getRawData428()!=null?chartData.getRawData428():"null")+",";
        rowDatas+=(chartData.getRawData429()!=null?chartData.getRawData429():"null")+",";
        rowDatas+=(chartData.getRawData430()!=null?chartData.getRawData430():"null")+",";
        rowDatas+=(chartData.getRawData431()!=null?chartData.getRawData431():"null")+",";
        rowDatas+=(chartData.getRawData432()!=null?chartData.getRawData432():"null")+",";
        rowDatas+=(chartData.getRawData433()!=null?chartData.getRawData433():"null")+",";
        rowDatas+=(chartData.getRawData434()!=null?chartData.getRawData434():"null")+",";
        rowDatas+=(chartData.getRawData435()!=null?chartData.getRawData435():"null")+",";
        rowDatas+=(chartData.getRawData436()!=null?chartData.getRawData436():"null")+",";
        rowDatas+=(chartData.getRawData437()!=null?chartData.getRawData437():"null")+",";
        rowDatas+=(chartData.getRawData438()!=null?chartData.getRawData438():"null")+",";
        rowDatas+=(chartData.getRawData439()!=null?chartData.getRawData439():"null")+",";
        rowDatas+=(chartData.getRawData440()!=null?chartData.getRawData440():"null")+",";
        rowDatas+=(chartData.getRawData441()!=null?chartData.getRawData441():"null")+",";
        rowDatas+=(chartData.getRawData442()!=null?chartData.getRawData442():"null")+",";
        rowDatas+=(chartData.getRawData443()!=null?chartData.getRawData443():"null")+",";
        rowDatas+=(chartData.getRawData444()!=null?chartData.getRawData444():"null")+",";
        rowDatas+=(chartData.getRawData445()!=null?chartData.getRawData445():"null")+",";
        rowDatas+=(chartData.getRawData446()!=null?chartData.getRawData446():"null")+",";
        rowDatas+=(chartData.getRawData447()!=null?chartData.getRawData447():"null")+",";
        rowDatas+=(chartData.getRawData448()!=null?chartData.getRawData448():"null")+",";
        rowDatas+=(chartData.getRawData449()!=null?chartData.getRawData449():"null")+",";
        rowDatas+=(chartData.getRawData450()!=null?chartData.getRawData450():"null")+",";
        rowDatas+=(chartData.getRawData451()!=null?chartData.getRawData451():"null")+",";
        rowDatas+=(chartData.getRawData452()!=null?chartData.getRawData452():"null")+",";
        rowDatas+=(chartData.getRawData453()!=null?chartData.getRawData453():"null")+",";
        rowDatas+=(chartData.getRawData454()!=null?chartData.getRawData454():"null")+",";
        rowDatas+=(chartData.getRawData455()!=null?chartData.getRawData455():"null")+",";
        rowDatas+=(chartData.getRawData456()!=null?chartData.getRawData456():"null")+",";
        rowDatas+=(chartData.getRawData457()!=null?chartData.getRawData457():"null")+",";
        rowDatas+=(chartData.getRawData458()!=null?chartData.getRawData458():"null")+",";
        rowDatas+=(chartData.getRawData459()!=null?chartData.getRawData459():"null")+",";
        rowDatas+=(chartData.getRawData460()!=null?chartData.getRawData460():"null")+",";
        rowDatas+=(chartData.getRawData461()!=null?chartData.getRawData461():"null")+",";
        rowDatas+=(chartData.getRawData462()!=null?chartData.getRawData462():"null")+",";
        rowDatas+=(chartData.getRawData463()!=null?chartData.getRawData463():"null")+",";
        rowDatas+=(chartData.getRawData464()!=null?chartData.getRawData464():"null")+",";
        rowDatas+=(chartData.getRawData465()!=null?chartData.getRawData465():"null")+",";
        rowDatas+=(chartData.getRawData466()!=null?chartData.getRawData466():"null")+",";
        rowDatas+=(chartData.getRawData467()!=null?chartData.getRawData467():"null")+",";
        rowDatas+=(chartData.getRawData468()!=null?chartData.getRawData468():"null")+",";
        rowDatas+=(chartData.getRawData469()!=null?chartData.getRawData469():"null")+",";
        rowDatas+=(chartData.getRawData470()!=null?chartData.getRawData470():"null")+",";
        rowDatas+=(chartData.getRawData471()!=null?chartData.getRawData471():"null")+",";
        rowDatas+=(chartData.getRawData472()!=null?chartData.getRawData472():"null")+",";
        rowDatas+=(chartData.getRawData473()!=null?chartData.getRawData473():"null")+",";
        rowDatas+=(chartData.getRawData474()!=null?chartData.getRawData474():"null")+",";
        rowDatas+=(chartData.getRawData475()!=null?chartData.getRawData475():"null")+",";
        rowDatas+=(chartData.getRawData476()!=null?chartData.getRawData476():"null")+",";
        rowDatas+=(chartData.getRawData477()!=null?chartData.getRawData477():"null")+",";
        rowDatas+=(chartData.getRawData478()!=null?chartData.getRawData478():"null")+",";
        rowDatas+=(chartData.getRawData479()!=null?chartData.getRawData479():"null")+",";
        rowDatas+=(chartData.getRawData480()!=null?chartData.getRawData480():"null")+",";
        rowDatas+=(chartData.getRawData481()!=null?chartData.getRawData481():"null")+",";
        rowDatas+=(chartData.getRawData482()!=null?chartData.getRawData482():"null")+",";
        rowDatas+=(chartData.getRawData483()!=null?chartData.getRawData483():"null")+",";
        rowDatas+=(chartData.getRawData484()!=null?chartData.getRawData484():"null")+",";
        rowDatas+=(chartData.getRawData485()!=null?chartData.getRawData485():"null")+",";
        rowDatas+=(chartData.getRawData486()!=null?chartData.getRawData486():"null")+",";
        rowDatas+=(chartData.getRawData487()!=null?chartData.getRawData487():"null")+",";
        rowDatas+=(chartData.getRawData488()!=null?chartData.getRawData488():"null")+",";
        rowDatas+=(chartData.getRawData489()!=null?chartData.getRawData489():"null")+",";
        rowDatas+=(chartData.getRawData490()!=null?chartData.getRawData490():"null")+",";
        rowDatas+=(chartData.getRawData491()!=null?chartData.getRawData491():"null")+",";
        rowDatas+=(chartData.getRawData492()!=null?chartData.getRawData492():"null")+",";
        rowDatas+=(chartData.getRawData493()!=null?chartData.getRawData493():"null")+",";
        rowDatas+=(chartData.getRawData494()!=null?chartData.getRawData494():"null")+",";
        rowDatas+=(chartData.getRawData495()!=null?chartData.getRawData495():"null")+",";
        rowDatas+=(chartData.getRawData496()!=null?chartData.getRawData496():"null")+",";
        rowDatas+=(chartData.getRawData497()!=null?chartData.getRawData497():"null")+",";
        rowDatas+=(chartData.getRawData498()!=null?chartData.getRawData498():"null")+",";
        rowDatas+=(chartData.getRawData499()!=null?chartData.getRawData499():"null")+",";
        rowDatas+=(chartData.getRawData500()!=null?chartData.getRawData500():"null")+",";
        rowDatas+=(chartData.getRawData501()!=null?chartData.getRawData501():"null")+",";
        rowDatas+=(chartData.getRawData502()!=null?chartData.getRawData502():"null")+",";
        rowDatas+=(chartData.getRawData503()!=null?chartData.getRawData503():"null")+",";
        rowDatas+=(chartData.getRawData504()!=null?chartData.getRawData504():"null")+",";
        rowDatas+=(chartData.getRawData505()!=null?chartData.getRawData505():"null")+",";
        rowDatas+=(chartData.getRawData506()!=null?chartData.getRawData506():"null")+",";
        rowDatas+=(chartData.getRawData507()!=null?chartData.getRawData507():"null")+",";
        rowDatas+=(chartData.getRawData508()!=null?chartData.getRawData508():"null")+",";
        rowDatas+=(chartData.getRawData509()!=null?chartData.getRawData509():"null")+",";
        rowDatas+=(chartData.getRawData510()!=null?chartData.getRawData510():"null")+",";
        rowDatas+=(chartData.getRawData511()!=null?chartData.getRawData511():"null")+",";
        rowDatas+=(chartData.getRawData512()!=null?chartData.getRawData512():"null")+",";
        rowDatas+=(chartData.getRawData513()!=null?chartData.getRawData513():"null")+",";
        rowDatas+=(chartData.getRawData514()!=null?chartData.getRawData514():"null")+",";
        rowDatas+=(chartData.getRawData515()!=null?chartData.getRawData515():"null")+",";
        rowDatas+=(chartData.getRawData516()!=null?chartData.getRawData516():"null")+",";
        rowDatas+=(chartData.getRawData517()!=null?chartData.getRawData517():"null")+",";
        rowDatas+=(chartData.getRawData518()!=null?chartData.getRawData518():"null")+",";
        rowDatas+=(chartData.getRawData519()!=null?chartData.getRawData519():"null")+",";
        rowDatas+=(chartData.getRawData520()!=null?chartData.getRawData520():"null")+",";
        rowDatas+=(chartData.getRawData521()!=null?chartData.getRawData521():"null")+",";
        rowDatas+=(chartData.getRawData522()!=null?chartData.getRawData522():"null")+",";
        rowDatas+=(chartData.getRawData523()!=null?chartData.getRawData523():"null")+",";
        rowDatas+=(chartData.getRawData524()!=null?chartData.getRawData524():"null")+",";
        rowDatas+=(chartData.getRawData525()!=null?chartData.getRawData525():"null")+",";
        rowDatas+=(chartData.getRawData526()!=null?chartData.getRawData526():"null")+",";
        rowDatas+=(chartData.getRawData527()!=null?chartData.getRawData527():"null")+",";
        rowDatas+=(chartData.getRawData528()!=null?chartData.getRawData528():"null")+",";
        rowDatas+=(chartData.getRawData529()!=null?chartData.getRawData529():"null")+",";
        rowDatas+=(chartData.getRawData530()!=null?chartData.getRawData530():"null")+",";
        rowDatas+=(chartData.getRawData531()!=null?chartData.getRawData531():"null")+",";
        rowDatas+=(chartData.getRawData532()!=null?chartData.getRawData532():"null")+",";
        rowDatas+=(chartData.getRawData533()!=null?chartData.getRawData533():"null")+",";
        rowDatas+=(chartData.getRawData534()!=null?chartData.getRawData534():"null")+",";
        rowDatas+=(chartData.getRawData535()!=null?chartData.getRawData535():"null")+",";
        rowDatas+=(chartData.getRawData536()!=null?chartData.getRawData536():"null")+",";
        rowDatas+=(chartData.getRawData537()!=null?chartData.getRawData537():"null")+",";
        rowDatas+=(chartData.getRawData538()!=null?chartData.getRawData538():"null")+",";
        rowDatas+=(chartData.getRawData539()!=null?chartData.getRawData539():"null")+",";
        rowDatas+=(chartData.getRawData540()!=null?chartData.getRawData540():"null")+",";
        rowDatas+=(chartData.getRawData541()!=null?chartData.getRawData541():"null")+",";
        rowDatas+=(chartData.getRawData542()!=null?chartData.getRawData542():"null")+",";
        rowDatas+=(chartData.getRawData543()!=null?chartData.getRawData543():"null")+",";
        rowDatas+=(chartData.getRawData544()!=null?chartData.getRawData544():"null")+",";
        rowDatas+=(chartData.getRawData545()!=null?chartData.getRawData545():"null")+",";
        rowDatas+=(chartData.getRawData546()!=null?chartData.getRawData546():"null")+",";
        rowDatas+=(chartData.getRawData547()!=null?chartData.getRawData547():"null")+",";
        rowDatas+=(chartData.getRawData548()!=null?chartData.getRawData548():"null")+",";
        rowDatas+=(chartData.getRawData549()!=null?chartData.getRawData549():"null")+",";
        rowDatas+=(chartData.getRawData550()!=null?chartData.getRawData550():"null")+",";
        rowDatas+=(chartData.getRawData551()!=null?chartData.getRawData551():"null")+",";
        rowDatas+=(chartData.getRawData552()!=null?chartData.getRawData552():"null")+",";
        rowDatas+=(chartData.getRawData553()!=null?chartData.getRawData553():"null")+",";
        rowDatas+=(chartData.getRawData554()!=null?chartData.getRawData554():"null")+",";
        rowDatas+=(chartData.getRawData555()!=null?chartData.getRawData555():"null")+",";
        rowDatas+=(chartData.getRawData556()!=null?chartData.getRawData556():"null")+",";
        rowDatas+=(chartData.getRawData557()!=null?chartData.getRawData557():"null")+",";
        rowDatas+=(chartData.getRawData558()!=null?chartData.getRawData558():"null")+",";
        rowDatas+=(chartData.getRawData559()!=null?chartData.getRawData559():"null")+",";
        rowDatas+=(chartData.getRawData560()!=null?chartData.getRawData560():"null")+",";
        rowDatas+=(chartData.getRawData561()!=null?chartData.getRawData561():"null")+",";
        rowDatas+=(chartData.getRawData562()!=null?chartData.getRawData562():"null")+",";
        rowDatas+=(chartData.getRawData563()!=null?chartData.getRawData563():"null")+",";
        rowDatas+=(chartData.getRawData564()!=null?chartData.getRawData564():"null")+",";
        rowDatas+=(chartData.getRawData565()!=null?chartData.getRawData565():"null")+",";
        rowDatas+=(chartData.getRawData566()!=null?chartData.getRawData566():"null")+",";
        rowDatas+=(chartData.getRawData567()!=null?chartData.getRawData567():"null")+",";
        rowDatas+=(chartData.getRawData568()!=null?chartData.getRawData568():"null")+",";
        rowDatas+=(chartData.getRawData569()!=null?chartData.getRawData569():"null")+",";
        rowDatas+=(chartData.getRawData570()!=null?chartData.getRawData570():"null")+",";
        rowDatas+=(chartData.getRawData571()!=null?chartData.getRawData571():"null")+",";
        rowDatas+=(chartData.getRawData572()!=null?chartData.getRawData572():"null")+",";
        rowDatas+=(chartData.getRawData573()!=null?chartData.getRawData573():"null")+",";
        rowDatas+=(chartData.getRawData574()!=null?chartData.getRawData574():"null")+",";
        rowDatas+=(chartData.getRawData575()!=null?chartData.getRawData575():"null")+",";
        rowDatas+=(chartData.getRawData576()!=null?chartData.getRawData576():"null")+",";
        rowDatas+=(chartData.getRawData577()!=null?chartData.getRawData577():"null")+",";
        rowDatas+=(chartData.getRawData578()!=null?chartData.getRawData578():"null")+",";
        rowDatas+=(chartData.getRawData579()!=null?chartData.getRawData579():"null")+",";
        rowDatas+=(chartData.getRawData580()!=null?chartData.getRawData580():"null")+",";
        rowDatas+=(chartData.getRawData581()!=null?chartData.getRawData581():"null")+",";
        rowDatas+=(chartData.getRawData582()!=null?chartData.getRawData582():"null")+",";
        rowDatas+=(chartData.getRawData583()!=null?chartData.getRawData583():"null")+",";
        rowDatas+=(chartData.getRawData584()!=null?chartData.getRawData584():"null")+",";
        rowDatas+=(chartData.getRawData585()!=null?chartData.getRawData585():"null")+",";
        rowDatas+=(chartData.getRawData586()!=null?chartData.getRawData586():"null")+",";
        rowDatas+=(chartData.getRawData587()!=null?chartData.getRawData587():"null")+",";
        rowDatas+=(chartData.getRawData588()!=null?chartData.getRawData588():"null")+",";
        rowDatas+=(chartData.getRawData589()!=null?chartData.getRawData589():"null")+",";
        rowDatas+=(chartData.getRawData590()!=null?chartData.getRawData590():"null")+",";
        rowDatas+=(chartData.getRawData591()!=null?chartData.getRawData591():"null")+",";
        rowDatas+=(chartData.getRawData592()!=null?chartData.getRawData592():"null")+",";
        rowDatas+=(chartData.getRawData593()!=null?chartData.getRawData593():"null")+",";
        rowDatas+=(chartData.getRawData594()!=null?chartData.getRawData594():"null")+",";
        rowDatas+=(chartData.getRawData595()!=null?chartData.getRawData595():"null")+",";
        rowDatas+=(chartData.getRawData596()!=null?chartData.getRawData596():"null")+",";
        rowDatas+=(chartData.getRawData597()!=null?chartData.getRawData597():"null")+",";
        rowDatas+=(chartData.getRawData598()!=null?chartData.getRawData598():"null")+",";
        rowDatas+=(chartData.getRawData599()!=null?chartData.getRawData599():"null")+",";
        rowDatas+=(chartData.getRawData600()!=null?chartData.getRawData600():"null");
        String[] arr=rowDatas.split(",");
        String result="";
        for(int i=0;i<count;i++){
            result+=arr[i]+",";
        }
        return result.substring(0,result.length()-1);

    }
    public  int saveControlDataListbyChartId(EtlConfD etlConfD,List<ControlData> controlDataList,String machineName,List<String> tableNameList) {
 //       logger.info("执行saveControlDataListbyChartId  machineName:{}", machineName);
        int count = 0;
        String tableName = "";
        String name = "CONTROLDATA_";
        String shop = etlConfD.getShop() + "_";
        String type = etlConfD.getCellType().equals("Inspection") ? "M_" : "P_";
        String eqpt = "";
//       if(tableNameList.size()<=0) {
        if (etlConfD.getCellType().equals("Inspection")) {

                eqpt = machineName.substring(0, 4);

        } else if (etlConfD.getCellType().equals("Production")) {
                eqpt = machineName.substring(0, 6);
        }
      /* }else{
           for(ControldataTablename tab:tableNameList){
               if((etlConfD.getShop().equals(tab.getShopId())) &&
                       etlConfD.getCellType().equals(tab.getDcType()) && machineName.equals(tab.getEqptId()) ){
                   tableName=tab.getTabname();
                   break;
               }
           }
           if(tableName.equals("")) tableName="CONTROLDATA_"+(etlConfD.getCellType().equals("Inspection")?"M":"P")+"_"+etlConfD.getShop()+"_OTHER";
       }*/
//        tableName="CONTROLDATA_M_OC_PIPR";
        tableName = name + type + shop + eqpt;
    if(tableNameList.contains(tableName)){

    }else{
        tableName = name + type + shop +"OTHER";
    }
        Session session = gpDao.getEntityManagerSession();
        try {
            StringBuffer sqlbuff = new StringBuffer();
            sqlbuff.append(" INSERT INTO sor." + tableName + " (" + CONTROLDATA_QUERY_COLUMN + ")VALUES ");
            for (ControlData controlData : controlDataList) {
                sqlbuff.append("(");
                sqlbuff.append(controlData.getChartid() == null ? "null" : "'" + controlData.getChartid() + "'").append(",");
                sqlbuff.append(controlData.getCharttype() == null ? "null" : "'" + controlData.getCharttype() + "'").append(",");
                sqlbuff.append(controlData.getFactoryname() == null ? "null" : "'" + controlData.getFactoryname() + "'").append(",");
                sqlbuff.append(controlData.getSubfactoryname() == null ? "null" : "'" + controlData.getSubfactoryname() + "'").append(",");
                sqlbuff.append(controlData.getControldataseq() == null ? "null" : "'" + controlData.getControldataseq() + "'").append(",");
                sqlbuff.append(controlData.getDcdataid() == null ? "null" : "'" + controlData.getDcdataid() + "'").append(",");
                sqlbuff.append(controlData.getDbTimestampSrc() == null ? "null" : "'" + controlData.getDbTimestampSrc() + "'").append(",");
                sqlbuff.append(controlData.getGlassId() == null ? "null" : "'" + controlData.getGlassId() + "'").append(",");
                sqlbuff.append(controlData.getEventtime() == null ? "null" : "'" + controlData.getEventtime() + "'").append(",");
                sqlbuff.append(controlData.getEventtimePre() == null ? "null" : "'" + controlData.getEventtimePre() + "'").append(",");
                sqlbuff.append(controlData.getItemname() == null ? "null" : "'" + controlData.getItemname() + "'").append(",");
                sqlbuff.append(controlData.getProductspecname() == null ? "null" : "'" + controlData.getProductspecname() + "'").append(",");
                sqlbuff.append(controlData.getEccode() == null ? "null" : "'" + controlData.getEccode() + "'").append(",");
                sqlbuff.append(controlData.getProcessoperationname() == null ? "null" : "'" + controlData.getProcessoperationname() + "'").append(",");
                sqlbuff.append(controlData.getMachinename() == null ? "null" : "'" + controlData.getMachinename() + "'").append(",");
                sqlbuff.append(controlData.getUnitname() == null ? "null" : "'" + controlData.getUnitname() + "'").append(",");
                sqlbuff.append(controlData.getRecipename() == null ? "null" : "'" + controlData.getRecipename() + "'").append(",");
                sqlbuff.append(controlData.getProcprocessoperationname() == null ? "null" : "'" + controlData.getProcprocessoperationname() + "'").append(",");
                sqlbuff.append(controlData.getProcmachinename() == null ? "null" : "'" + controlData.getProcmachinename() + "'").append(",");
                sqlbuff.append(controlData.getProcunitname() == null ? "null" : "'" + controlData.getProcunitname() + "'").append(",");
                sqlbuff.append(controlData.getProcrecipename() == null ? "null" : "'" + controlData.getProcrecipename() + "'").append(",");
                sqlbuff.append(controlData.getRunmode() == null ? "null" : "'" + controlData.getRunmode() + "'").append(",");
                sqlbuff.append(controlData.getOwnercode() == null ? "null" : "'" + controlData.getOwnercode() + "'").append(",");
                sqlbuff.append(controlData.getRtc() == null ? "null" : "'" + controlData.getRtc() + "'").append(",");
                sqlbuff.append(controlData.getQtap() == null ? "null" : "'" + controlData.getQtap() + "'").append(",");
                sqlbuff.append(controlData.getUspec() == null ? "null" : "'" + controlData.getUspec() + "'").append(",");
                sqlbuff.append(controlData.getTarget() == null ? "null" : "'" + controlData.getTarget() + "'").append(",");
                sqlbuff.append(controlData.getLspec() == null ? "null" : "'" + controlData.getLspec() + "'").append(",");
                sqlbuff.append(controlData.getUcl1() == null ? "null" : "'" + controlData.getUcl1() + "'").append(",");
                sqlbuff.append(controlData.getCl1() == null ? "null" : "'" + controlData.getCl1() + "'").append(",");
                sqlbuff.append(controlData.getLcl1() == null ? "null" : "'" + controlData.getLcl1() + "'").append(",");
                sqlbuff.append(controlData.getUcl2() == null ? "null" : "'" + controlData.getUcl2() + "'").append(",");
                sqlbuff.append(controlData.getCl2() == null ? "null" : "'" + controlData.getCl2() + "'").append(",");
                sqlbuff.append(controlData.getLcl2() == null ? "null" : "'" + controlData.getLcl2() + "'").append(",");
                sqlbuff.append(controlData.getUcl3() == null ? "null" : "'" + controlData.getUcl3() + "'").append(",");
                sqlbuff.append(controlData.getCl3() == null ? "null" : "'" + controlData.getCl3() + "'").append(",");
                sqlbuff.append(controlData.getLcl3() == null ? "null" : "'" + controlData.getLcl3() + "'").append(",");
                sqlbuff.append(controlData.getdXbar() == null ? "null" : "'" + controlData.getdXbar() + "'").append(",");
                sqlbuff.append(controlData.getdSdev() == null ? "null" : "'" + controlData.getdSdev() + "'").append(",");
                sqlbuff.append(controlData.getdRng() == null ? "null" : "'" + controlData.getdRng() + "'").append(",");
                sqlbuff.append(controlData.getdUnf1() == null ? "null" : "'" + controlData.getdUnf1() + "'").append(",");
                sqlbuff.append(controlData.getdUnf2() == null ? "null" : "'" + controlData.getdUnf2() + "'").append(",");
                sqlbuff.append(controlData.getdMax() == null ? "null" : "'" + controlData.getdMax() + "'").append(",");
                sqlbuff.append(controlData.getdMin() == null ? "null" : "'" + controlData.getdMin() + "'").append(",");
                sqlbuff.append(controlData.getdX() == null ? "null" : "'" + controlData.getdX() + "'").append(",");
                sqlbuff.append(controlData.getdRm() == null ? "null" : "'" + controlData.getdRm() + "'").append(",");
                sqlbuff.append(controlData.getdNp() == null ? "null" : "'" + controlData.getdNp() + "'").append(",");
                sqlbuff.append(controlData.getdP() == null ? "null" : "'" + controlData.getdP() + "'").append(",");
                sqlbuff.append(controlData.getdC() == null ? "null" : "'" + controlData.getdC() + "'").append(",");
                sqlbuff.append(controlData.getdU() == null ? "null" : "'" + controlData.getdU() + "'").append(",");
                sqlbuff.append(controlData.getDelMark() == null ? "null" : "'" + controlData.getDelMark() + "'").append(",");
                sqlbuff.append(controlData.getOos() == null ? "null" : "'" + controlData.getOos() + "'").append(",");
                sqlbuff.append(controlData.getOoc1() == null ? "null" : "'" + controlData.getOoc1() + "'").append(",");
                sqlbuff.append(controlData.getOoc2() == null ? "null" : "'" + controlData.getOoc2() + "'").append(",");
                sqlbuff.append(controlData.getOoc3() == null ? "null" : "'" + controlData.getOoc3() + "'").append(",");
                sqlbuff.append(controlData.getOor1() == null ? "null" : "'" + controlData.getOor1() + "'").append(",");
                sqlbuff.append(controlData.getOor2() == null ? "null" : "'" + controlData.getOor2() + "'").append(",");
                sqlbuff.append(controlData.getOor3() == null ? "null" : "'" + controlData.getOor3() + "'").append(",");
                sqlbuff.append(controlData.getSitecount() == null ? "null" : "'" + controlData.getSitecount() + "'").append(",");
                sqlbuff.append(controlData.getRawdatas() == null ? "null" : "'" + controlData.getRawdatas() + "'");
                sqlbuff.append("),");
            }
            sqlbuff.deleteCharAt(sqlbuff.length() - 1);
            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            count = queryLoadStep.executeUpdate();
            return count;

        } catch (Exception e) {
            logger.error("error {}", e);
        }
        return count;
    }

    public List<String> getControlDataTableNames(){
        List<String> list=new ArrayList<>();
        try{
            list =gpDao.findBySQL(" select upper(table_name) table_name from information_schema.tables" +
                    " where table_name like 'controldata%' ");
        }catch (Exception e){
            logger.error(" 获取 controlData tableName error {}",e);
            throw e;
        }
        return list;
    }




//******************************************************替换 SPC_表


    public List<ChartType> getChartTypeByChartIdX(EtlConfD etlConfD)throws Exception{
        //      logger.info("执行getChartTypeByChartId()");
        List<ChartType> list =new ArrayList<>();
        String shopId =etlConfD.getShop();
        String eqpt   =etlConfD.getEqpt();
        String dcType =etlConfD.getCellType();

        StringBuffer str =new StringBuffer();
        try{
            str.append(" SELECT CtrlC.CHARTID, CtrlC.CONTROLCHARTTYPE   ,CTD.FACTORYNAME, CTD.SUBFACTORYNAME,CTD.DCType,  CTD.PROCPROCESSOPERATIONNAME ,  ");
            str.append(" CTD.PROCMACHINENAME ,  CTD.PROCUNITNAME ,  CTD.PROCRECIPENAME ,EV.UDF1 AS ITEM1,EV.UDF2 AS ITEM2,EV.UDF3 AS ITEM3 ");
            str.append(" FROM  SPC_CONTROLCHART CtrlC, SPC_ENUMDEFVALUE EV, SPC_ChartDef CTD  WHERE  1=1 ");
            //         str.append(" and CtrlC.CHARTID = '495A1_A1870_WERP01_CD1_LOSS'  ");
            str.append(" and CtrlC.CHARTID in( SELECT /*+ INDEX(CD CAPABILITYDATA_IDX1) */ distinct   CD.CHARTID  FROM   SPC_CONTROLDATA CD,  SPC_ChartDef CF  WHERE   Cf.chartid=CD.chartid ");
            str.append(" AND CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
            if(StringUtils.isNotBlank(shopId)){
                str.append("AND CF.Factoryname='"+shopId+"' ");
            }
            if(StringUtils.isNotBlank(eqpt)){
                str.append("AND CF.Machinename='"+eqpt+"' ");
            }
            str.append(" )and CTD.FACTORYNAME ='"+shopId+"'  and CTD.DCType='"+dcType+"'     ");
            str.append(" AND EV .ENUMVALUE = CtrlC.CONTROLCHARTTYPE  and CTD.CHARTID =CtrlC.CHARTID  AND EV.ACTIVEFLAG = 'Y' and enumname='ControlChartType' ORDER BY CtrlC.CHARTID ");

            long startTime=System.currentTimeMillis();
            list=ora_spc.query(str.toString(),new BeanListHandler<ChartType>(ChartType.class));
            long endTime=System.currentTimeMillis();
            logger.info("获取 chartType success count :{}----time:{} ms",list.size(),(endTime-startTime));
            return list;
        }catch (Exception e){
            logger.error("获取 chartType error :{}----sql:{}",e.getMessage(),str.toString());
            throw new Exception("获取 chartType Exception:"+ e.getMessage());
        }


    }
    public List<ChartData> getChartDataByChartIdX(EtlConfD etlConfD, String chartId)throws Exception{
        //      logger.info("执行getChartDataByChartId {}",chartId);
        List<ChartData> list =new ArrayList<>();
        String sql ="";
        StringBuffer str =new StringBuffer();
//        String panelInCondition = set2InCondition(charIdList,"CD.CHARTID");
        try{
            sql+=" SELECT                                                                                    "+
                    " 	*                                                                                       "+
                    " FROM                                                                                      "+
                    " 	(                                                                                       "+
                    " 		SELECT                                                                              "+
                    " 			AA.*                                                                            "+
                    " 		FROM                                                                                "+
                    " 			(                                                                               "+
                    " 				SELECT A.CHARTID,                                                           "+
                    " 					A.CONTROLDATASEQ,                                                "+
                    " 					TO_DATE( SUBSTR(A.TIMEKEY,0,14), 'YYYY-MM-DD HH24:MI:SS' ) AS TIMEKEY,   "+
                    " 					DD.DCDATAID,                                                            "+
                    " 					DD.MATERIALNAME,                                                        "+
                    " 					TO_CHAR( DD.PROCESSTIME, 'YYYY-MM-DD HH24:MI:SS' ) AS PROCESSTIME,      "+
                    " 					TO_CHAR( DD.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS' ) AS EVENTTIME,          "+
                    " 					CASE                                                                    "+
                    " 						WHEN MD.MARKINGTIME IS NOT NULL THEN 'Y'                            "+
                    " 						ELSE 'N'                                                            "+
                    " 					END AS MARKINGFLAG,                                                     "+
                    " 					DD.PRODUCTSPECNAME,                                                     "+
                    " 					DD.ECCODE,                                                              "+
                    " 					DD.PROCESSOPERATIONNAME,                                                "+
                    " 					DD.MACHINENAME,                                                         "+
                    " 					DD.UNITNAME,                                                            "+
                    " 					DD.SUBUNITNAME,                                                         "+
                    " 					DD.RECIPENAME,                                                          "+
                    " 					DD.RUNMODE,                                                             "+
                    " 					DD.OWNERCODE,                                                           "+
                    " 					DD.RTC,                                                                 "+
                    " 					DD.QTAP,                                                                "+
                    " 					DI.ITEMNAME,                                                            "+
                    " 					DI.ALIASITEMNAME AS ALIASITEMNAME,                                      "+
                    " 					LISTAGG(                                                                "+
                    " 						DR.PROCPROCESSOPERATIONNAME,                                        "+
                    " 						','                                                                 "+
                    " 					) WITHIN GROUP(                                                         "+
                    " 					ORDER BY                                                                "+
                    " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                    " 					) AS PROCPROCESSOPERATIONNAME,                                          "+
                    " 					LISTAGG(                                                                "+
                    " 						DR.PROCRECIPENAME,                                                  "+
                    " 						','                                                                 "+
                    " 					) WITHIN GROUP(                                                         "+
                    " 					ORDER BY                                                                "+
                    " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                    " 					) AS PROCRECIPENAME,                                                    "+
                    " 					LISTAGG(                                                                "+
                    " 						DR.PROCMACHINENAME,                                                 "+
                    " 						','                                                                 "+
                    " 					) WITHIN GROUP(                                                         "+
                    " 					ORDER BY                                                                "+
                    " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                    " 					) AS PROCMACHINENAME,                                                   "+
                    " 					LISTAGG(                                                                "+
                    " 						DR.PROCUNITNAME,                                                    "+
                    " 						','                                                                 "+
                    " 					) WITHIN GROUP(                                                         "+
                    " 					ORDER BY                                                                "+
                    " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                    " 					) AS PROCUNITNAME,                                                      "+
                    " 					LISTAGG(                                                                "+
                    " 						DR.PROCSUBUNITNAME,                                                 "+
                    " 						','                                                                 "+
                    " 					) WITHIN GROUP(                                                         "+
                    " 					ORDER BY                                                                "+
                    " 						DR.PROCPROCESSOPERATIONNAME                                         "+
                    " 					) AS PROCSUBUNITNAME,                                                   "+
                    " 					MAX( A.ALARMTYPE ) AS ALARMTYPE,                                        "+
                    " 					A.XBAR,                                                                 "+
                    " 					A.S,                                                                    "+
                    " 					A.R,                                                                    "+
                    " 					A.U1,                                                                   "+
                    " 					A.U2,                                                                   "+
                    " 					A.MIN,                                                                  "+
                    " 					A.MAX,                                                                  "+
                    " 					A.I,                                                                    "+
                    " 					A.MR,                                                                   "+
                    " 					A.NP,                                                                   "+
                    " 					A.P,                                                                    "+
                    " 					A.C,                                                                    "+
                    " 					A.U,                                                                    "+
                    " 					COUNT( CDR.SITENAME ) OVER(                                             "+
                    " 						PARTITION BY A.CONTROLDATASEQ                                       "+
                    " 					) AS SITECOUNT,                                                         "+
                    " 					'RAWDATA' || LPAD( RANK() OVER( PARTITION BY A.CONTROLDATASEQ ORDER BY CDR.SITENAME ), 3, '0' ) AS SITENAMESEQ,"+
                    " 					CDR.RESULT AS SITERESULT                                              "+
                    " 				FROM                                                                      "+
                    " 					(                                                                     "+
                    " 						SELECT                                                            "+
                    " 							*                                                             "+
                    " 						FROM                                                              "+
                    " 							(                                                             "+
                    " 								SELECT                                                    "+
                    " 									CD.CHARTID,                                           "+
                    " 									CD.CONTROLDATASEQ,                                    "+
                    " 									CD.DCDATAID,                                          "+
                    " 									CD.TIMEKEY,                                           "+
                    " 									CDS.CHARTNAME,                                        "+
                    " 									NVL( CDR.RESULT, 'NA' ) AS RESULT,                    "+
                    " 									MAX( MAX( AR.ALARMTYPE )) OVER(                       "+
                    " 										PARTITION BY CD.CHARTID,                          "+
                    " 										CD.CONTROLDATASEQ,                                "+
                    " 										CD.DCDATAID                                       "+
                    " 									) AS ALARMTYPE                                        "+
                    " 								FROM                                                      "+
                    " 									(                                                     "+
                    " 										SELECT                                            "+
                    " 											/*+ INDEX(CD CONTROLDATA_IDX1) */               "+
                    " 											ROWNUM AS DATASEQ,                            "+
                    " 											CD.CHARTID,                                   "+
                    " 											CD.CONTROLDATASEQ,                            "+
                    " 											CD.DCDATAID,                                  "+
                    " 											CD.TIMEKEY                                    "+
                    " 										FROM                                              "+
                    " 											SPC_CONTROLDATA CD                                "+
                    " 										WHERE  1=1                                        ";
//         str.append(" and CD.CHARTID = 'PIPR03-PIIN-495P2-U'  ");
            str.append(" and CD.CHARTID = '"+chartId+"'  ");
//            str.append(" and CD.controldataseq = '20180601000125817001'  ");
            //           str.append(" AND CD.TIMEKEY >= '20180601000000' AND CD.TIMEKEY < '20180601000500' ");
            str.append(" and CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())
                    +"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
            sql+=str.toString();

            sql+=	" 								) CD                                          "+
                    " 							INNER JOIN SPC_CONTROLDATASPEC CDS ON                 "+
                    " 								CDS.CHARTID = CD.CHARTID                      "+
                    " 								AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ    "+
                    " 							INNER JOIN SPC_ENUMDEFVALUE EDV ON                    "+
                    " 								EDV.ENUMNAME = CDS.CONTROLCHARTTYPE           "+
                    " 								AND EDV.ENUMVALUE = CDS.CHARTNAME             "+
                    " 							LEFT JOIN SPC_CONTROLDATARESULT CDR ON                "+
                    " 								CDR.CHARTID = CD.CHARTID                      "+
                    " 								AND CDR.CONTROLDATASEQ = CD.CONTROLDATASEQ    "+
                    " 								AND CDR.SITENAME = EDV.UDF1                   "+
                    " 							LEFT JOIN SPC_ALARMDATA AR ON                         "+
                    " 								CDR.CHARTID = AR.CHARTID                      "+
                    " 								AND CDR.CONTROLDATASEQ = AR.CONTROLDATASEQ    "+
                    " 								AND EDV.UDF1 = AR.CHARTNAME                   "+
                    " 							WHERE                                             "+
                    " 								1 = 1                                         "+
                    " 							GROUP BY                                          "+
                    " 								CD.CHARTID,                                   "+
                    " 								CD.CONTROLDATASEQ,                            "+
                    " 								CD.DCDATAID,                                  "+
                    " 								CD.TIMEKEY,                                   "+
                    " 								CDS.CHARTNAME,                                "+
                    " 								NVL( CDR.RESULT, 'NA' )                       "+
                    " 						) PIVOT(                                              "+
                    " 							MAX( RESULT ) FOR CHARTNAME IN(                   "+
                    " 								'Xbar' AS XBAR,                               "+
                    " 								'S' AS S,                                     "+
                    " 								'R' AS R,                                     "+
                    " 								'U1' AS U1,                                   "+
                    " 								'U2' AS U2,                                   "+
                    " 								'Min' AS MIN,                                 "+
                    " 								'Max' AS MAX,                                 "+
                    " 								'I' AS I,                                     "+
                    " 								'mR' AS MR,                                   "+
                    " 								'NP' AS NP,                                   "+
                    " 								'P' AS P,                                     "+
                    " 								'C' AS C,                                     "+
                    " 								'U' AS U                                      "+
                    " 							)                                                 "+
                    " 						)                                                     "+
                    " 					ORDER BY                                                  "+
                    " 						CONTROLDATASEQ                                        "+
                    " 				) A                                                           "+
                    " 			INNER JOIN SPC_CHARTDEF CH ON                                         "+
                    " 				CH.CHARTID = A.CHARTID                                        "+
                    " 			INNER JOIN SPC_DCDATA DD ON                                           "+
                    " 				DD.DCDATAID = A.DCDATAID                                      "+
                    " 			LEFT JOIN SPC_DCDEF DC ON                                             "+
                    " 				DC.FACTORYNAME = DD.FACTORYNAME                               "+
                    " 				AND DC.MACHINENAME = DD.MACHINENAME                           "+
                    " 				AND NVL( DC.UNITNAME, '-' )= NVL( DD.UNITNAME, '-' )          "+
                    " 			LEFT JOIN SPC_DCITEM DI ON                                            "+
                    " 				DI.DCDEFNAME = DC.DCDEFNAME                                   "+
                    " 				AND DI.ALIASITEMNAME = CH.ALIASITEMNAME                       "+
                    " 			LEFT JOIN SPC_MARKINGDATA MD ON                                       "+
                    " 				MD.CHARTID = A.CHARTID                                        "+
                    " 				AND MD.CONTROLDATASEQ = A.CONTROLDATASEQ                      "+
                    " 			LEFT JOIN SPC_DCPOREFERENCEDATA DR ON                                 "+
                    " 				DR.DCDATAID = A.DCDATAID                                      "+
                    " 			LEFT JOIN SPC_CONTROLDATARESULT CDR ON                                "+
                    " 				CDR.CHARTID = A.CHARTID                                       "+
                    " 				AND CDR.CONTROLDATASEQ = A.CONTROLDATASEQ                     "+
                    " 				AND CDR.SITETYPE = 'Raw'                                      "+
                    " 			GROUP BY                                                          "+
                    " 			A.CHARTID,                                                        "+
                    " 				A.CONTROLDATASEQ,                                             "+
                    " 				A.TIMEKEY,                                                    "+
                    " 				DD.DCDATAID,                                                  "+
                    " 				DD.MATERIALNAME,                                              "+
                    " 				DD.PROCESSTIME,                                               "+
                    " 				DD.EVENTTIME,                                                 "+
                    " 				MD.MARKINGTIME,                                               "+
                    " 				DD.PRODUCTSPECNAME,                                           "+
                    " 				DD.ECCODE,                                                    "+
                    " 				DD.PROCESSOPERATIONNAME,                                      "+
                    " 				DD.MACHINENAME,                                               "+
                    " 				DD.UNITNAME,                                                  "+
                    " 				DD.SUBUNITNAME,                                               "+
                    " 				DD.RECIPENAME,                                                "+
                    " 				DD.RUNMODE,                                                   "+
                    " 				DD.OWNERCODE,                                                 "+
                    " 				DD.RTC,                                                       "+
                    " 				DD.QTAP,                                                      "+
                    " 				DI.ITEMNAME,                                                  "+
                    " 				DI.ALIASITEMNAME,                                             "+
                    " 				A.XBAR,                                                       "+
                    " 				A.S,                                                          "+
                    " 				A.R,                                                          "+
                    " 				A.U1,                                                         "+
                    " 				A.U2,                                                         "+
                    " 				A.MIN,                                                        "+
                    " 				A.MAX,                                                        "+
                    " 				A.I,                                                          "+
                    " 				A.MR,                                                         "+
                    " 				A.NP,                                                         "+
                    " 				A.P,                                                          "+
                    " 				A.C,                                                          "+
                    " 				A.U,                                                          "+
                    " 				CDR.SITENAME,                                                 "+
                    " 				CDR.RESULT                                                    "+
                    " 		) AA                                                                  "+
                    " ) PIVOT(                                                                    "+
                    "	MAX( SITERESULT ) FOR SITENAMESEQ IN(                                     "+
                    " 'RAWDATA001' AS RAWDATA001, 'RAWDATA002' AS RAWDATA002, 'RAWDATA003' AS RAWDATA003, 'RAWDATA004' AS RAWDATA004,                      "+
                    " 'RAWDATA005' AS RAWDATA005, 'RAWDATA006' AS RAWDATA006, 'RAWDATA007' AS RAWDATA007, 'RAWDATA008' AS RAWDATA008,                        "+
                    " 'RAWDATA009' AS RAWDATA009, 'RAWDATA010' AS RAWDATA010, 'RAWDATA011' AS RAWDATA011, 'RAWDATA012' AS RAWDATA012,                        "+
                    " 'RAWDATA013' AS RAWDATA013, 'RAWDATA014' AS RAWDATA014, 'RAWDATA015' AS RAWDATA015, 'RAWDATA016' AS RAWDATA016,                        "+
                    " 'RAWDATA017' AS RAWDATA017, 'RAWDATA018' AS RAWDATA018, 'RAWDATA019' AS RAWDATA019, 'RAWDATA020' AS RAWDATA020,                        "+
                    " 'RAWDATA021' AS RAWDATA021, 'RAWDATA022' AS RAWDATA022, 'RAWDATA023' AS RAWDATA023, 'RAWDATA024' AS RAWDATA024,                        "+
                    " 'RAWDATA025' AS RAWDATA025, 'RAWDATA026' AS RAWDATA026, 'RAWDATA027' AS RAWDATA027, 'RAWDATA028' AS RAWDATA028,                        "+
                    " 'RAWDATA029' AS RAWDATA029, 'RAWDATA030' AS RAWDATA030, 'RAWDATA031' AS RAWDATA031, 'RAWDATA032' AS RAWDATA032,                        "+
                    " 'RAWDATA033' AS RAWDATA033, 'RAWDATA034' AS RAWDATA034, 'RAWDATA035' AS RAWDATA035, 'RAWDATA036' AS RAWDATA036,                        "+
                    " 'RAWDATA037' AS RAWDATA037, 'RAWDATA038' AS RAWDATA038, 'RAWDATA039' AS RAWDATA039, 'RAWDATA040' AS RAWDATA040,                        "+
                    " 'RAWDATA041' AS RAWDATA041, 'RAWDATA042' AS RAWDATA042, 'RAWDATA043' AS RAWDATA043, 'RAWDATA044' AS RAWDATA044,                        "+
                    " 'RAWDATA045' AS RAWDATA045, 'RAWDATA046' AS RAWDATA046, 'RAWDATA047' AS RAWDATA047, 'RAWDATA048' AS RAWDATA048,                        "+
                    " 'RAWDATA049' AS RAWDATA049, 'RAWDATA050' AS RAWDATA050, 'RAWDATA051' AS RAWDATA051, 'RAWDATA052' AS RAWDATA052,                        "+
                    " 'RAWDATA053' AS RAWDATA053, 'RAWDATA054' AS RAWDATA054, 'RAWDATA055' AS RAWDATA055, 'RAWDATA056' AS RAWDATA056,                        "+
                    " 'RAWDATA057' AS RAWDATA057, 'RAWDATA058' AS RAWDATA058, 'RAWDATA059' AS RAWDATA059, 'RAWDATA060' AS RAWDATA060,                        "+
                    " 'RAWDATA061' AS RAWDATA061, 'RAWDATA062' AS RAWDATA062, 'RAWDATA063' AS RAWDATA063, 'RAWDATA064' AS RAWDATA064,                        "+
                    " 'RAWDATA065' AS RAWDATA065, 'RAWDATA066' AS RAWDATA066, 'RAWDATA067' AS RAWDATA067, 'RAWDATA068' AS RAWDATA068,                        "+
                    " 'RAWDATA069' AS RAWDATA069, 'RAWDATA070' AS RAWDATA070, 'RAWDATA071' AS RAWDATA071, 'RAWDATA072' AS RAWDATA072,                        "+
                    " 'RAWDATA073' AS RAWDATA073, 'RAWDATA074' AS RAWDATA074, 'RAWDATA075' AS RAWDATA075, 'RAWDATA076' AS RAWDATA076,                        "+
                    " 'RAWDATA077' AS RAWDATA077, 'RAWDATA078' AS RAWDATA078, 'RAWDATA079' AS RAWDATA079, 'RAWDATA080' AS RAWDATA080,                        "+
                    " 'RAWDATA081' AS RAWDATA081, 'RAWDATA082' AS RAWDATA082, 'RAWDATA083' AS RAWDATA083, 'RAWDATA084' AS RAWDATA084,                        "+
                    " 'RAWDATA085' AS RAWDATA085, 'RAWDATA086' AS RAWDATA086, 'RAWDATA087' AS RAWDATA087, 'RAWDATA088' AS RAWDATA088,                        "+
                    " 'RAWDATA089' AS RAWDATA089, 'RAWDATA090' AS RAWDATA090, 'RAWDATA091' AS RAWDATA091, 'RAWDATA092' AS RAWDATA092,                        "+
                    " 'RAWDATA093' AS RAWDATA093, 'RAWDATA094' AS RAWDATA094, 'RAWDATA095' AS RAWDATA095, 'RAWDATA096' AS RAWDATA096,                        "+
                    " 'RAWDATA097' AS RAWDATA097, 'RAWDATA098' AS RAWDATA098, 'RAWDATA099' AS RAWDATA099, 'RAWDATA100' AS RAWDATA100,                        "+
                    " 'RAWDATA101' AS RAWDATA101, 'RAWDATA102' AS RAWDATA102, 'RAWDATA103' AS RAWDATA103, 'RAWDATA104' AS RAWDATA104,                        "+
                    " 'RAWDATA105' AS RAWDATA105, 'RAWDATA106' AS RAWDATA106, 'RAWDATA107' AS RAWDATA107, 'RAWDATA108' AS RAWDATA108,                        "+
                    " 'RAWDATA109' AS RAWDATA109, 'RAWDATA110' AS RAWDATA110, 'RAWDATA111' AS RAWDATA111, 'RAWDATA112' AS RAWDATA112,                        "+
                    " 'RAWDATA113' AS RAWDATA113, 'RAWDATA114' AS RAWDATA114, 'RAWDATA115' AS RAWDATA115, 'RAWDATA116' AS RAWDATA116,                        "+
                    " 'RAWDATA117' AS RAWDATA117, 'RAWDATA118' AS RAWDATA118, 'RAWDATA119' AS RAWDATA119, 'RAWDATA120' AS RAWDATA120,                        "+
                    " 'RAWDATA121' AS RAWDATA121, 'RAWDATA122' AS RAWDATA122, 'RAWDATA123' AS RAWDATA123, 'RAWDATA124' AS RAWDATA124,                        "+
                    " 'RAWDATA125' AS RAWDATA125, 'RAWDATA126' AS RAWDATA126, 'RAWDATA127' AS RAWDATA127, 'RAWDATA128' AS RAWDATA128,                        "+
                    " 'RAWDATA129' AS RAWDATA129, 'RAWDATA130' AS RAWDATA130, 'RAWDATA131' AS RAWDATA131, 'RAWDATA132' AS RAWDATA132,                        "+
                    " 'RAWDATA133' AS RAWDATA133, 'RAWDATA134' AS RAWDATA134, 'RAWDATA135' AS RAWDATA135, 'RAWDATA136' AS RAWDATA136,                        "+
                    " 'RAWDATA137' AS RAWDATA137, 'RAWDATA138' AS RAWDATA138, 'RAWDATA139' AS RAWDATA139, 'RAWDATA140' AS RAWDATA140,                        "+
                    " 'RAWDATA141' AS RAWDATA141, 'RAWDATA142' AS RAWDATA142, 'RAWDATA143' AS RAWDATA143, 'RAWDATA144' AS RAWDATA144,                        "+
                    " 'RAWDATA145' AS RAWDATA145, 'RAWDATA146' AS RAWDATA146, 'RAWDATA147' AS RAWDATA147, 'RAWDATA148' AS RAWDATA148,                        "+
                    " 'RAWDATA149' AS RAWDATA149, 'RAWDATA150' AS RAWDATA150, 'RAWDATA151' AS RAWDATA151, 'RAWDATA152' AS RAWDATA152,                        "+
                    " 'RAWDATA153' AS RAWDATA153, 'RAWDATA154' AS RAWDATA154, 'RAWDATA155' AS RAWDATA155, 'RAWDATA156' AS RAWDATA156,                        "+
                    " 'RAWDATA157' AS RAWDATA157, 'RAWDATA158' AS RAWDATA158, 'RAWDATA159' AS RAWDATA159, 'RAWDATA160' AS RAWDATA160,                        "+
                    " 'RAWDATA161' AS RAWDATA161, 'RAWDATA162' AS RAWDATA162, 'RAWDATA163' AS RAWDATA163, 'RAWDATA164' AS RAWDATA164,                        "+
                    " 'RAWDATA165' AS RAWDATA165, 'RAWDATA166' AS RAWDATA166, 'RAWDATA167' AS RAWDATA167, 'RAWDATA168' AS RAWDATA168,                        "+
                    " 'RAWDATA169' AS RAWDATA169, 'RAWDATA170' AS RAWDATA170, 'RAWDATA171' AS RAWDATA171, 'RAWDATA172' AS RAWDATA172,                        "+
                    " 'RAWDATA173' AS RAWDATA173, 'RAWDATA174' AS RAWDATA174, 'RAWDATA175' AS RAWDATA175, 'RAWDATA176' AS RAWDATA176,                        "+
                    " 'RAWDATA177' AS RAWDATA177, 'RAWDATA178' AS RAWDATA178, 'RAWDATA179' AS RAWDATA179, 'RAWDATA180' AS RAWDATA180,                        "+
                    " 'RAWDATA181' AS RAWDATA181, 'RAWDATA182' AS RAWDATA182, 'RAWDATA183' AS RAWDATA183, 'RAWDATA184' AS RAWDATA184,                        "+
                    " 'RAWDATA185' AS RAWDATA185, 'RAWDATA186' AS RAWDATA186, 'RAWDATA187' AS RAWDATA187, 'RAWDATA188' AS RAWDATA188,                        "+
                    " 'RAWDATA189' AS RAWDATA189, 'RAWDATA190' AS RAWDATA190, 'RAWDATA191' AS RAWDATA191, 'RAWDATA192' AS RAWDATA192,                        "+
                    " 'RAWDATA193' AS RAWDATA193, 'RAWDATA194' AS RAWDATA194, 'RAWDATA195' AS RAWDATA195, 'RAWDATA196' AS RAWDATA196,                        "+
                    " 'RAWDATA197' AS RAWDATA197, 'RAWDATA198' AS RAWDATA198, 'RAWDATA199' AS RAWDATA199, 'RAWDATA200' AS RAWDATA200,                        "+
                    " 'RAWDATA201' AS RAWDATA201, 'RAWDATA202' AS RAWDATA202, 'RAWDATA203' AS RAWDATA203, 'RAWDATA204' AS RAWDATA204,                        "+
                    " 'RAWDATA205' AS RAWDATA205, 'RAWDATA206' AS RAWDATA206, 'RAWDATA207' AS RAWDATA207, 'RAWDATA208' AS RAWDATA208,                        "+
                    " 'RAWDATA209' AS RAWDATA209, 'RAWDATA210' AS RAWDATA210, 'RAWDATA211' AS RAWDATA211, 'RAWDATA212' AS RAWDATA212,                        "+
                    " 'RAWDATA213' AS RAWDATA213, 'RAWDATA214' AS RAWDATA214, 'RAWDATA215' AS RAWDATA215, 'RAWDATA216' AS RAWDATA216,                        "+
                    " 'RAWDATA217' AS RAWDATA217, 'RAWDATA218' AS RAWDATA218, 'RAWDATA219' AS RAWDATA219, 'RAWDATA220' AS RAWDATA220,                        "+
                    " 'RAWDATA221' AS RAWDATA221, 'RAWDATA222' AS RAWDATA222, 'RAWDATA223' AS RAWDATA223, 'RAWDATA224' AS RAWDATA224,                        "+
                    " 'RAWDATA225' AS RAWDATA225, 'RAWDATA226' AS RAWDATA226, 'RAWDATA227' AS RAWDATA227, 'RAWDATA228' AS RAWDATA228,                        "+
                    " 'RAWDATA229' AS RAWDATA229, 'RAWDATA230' AS RAWDATA230, 'RAWDATA231' AS RAWDATA231, 'RAWDATA232' AS RAWDATA232,                        "+
                    " 'RAWDATA233' AS RAWDATA233, 'RAWDATA234' AS RAWDATA234, 'RAWDATA235' AS RAWDATA235, 'RAWDATA236' AS RAWDATA236,                        "+
                    " 'RAWDATA237' AS RAWDATA237, 'RAWDATA238' AS RAWDATA238, 'RAWDATA239' AS RAWDATA239, 'RAWDATA240' AS RAWDATA240,                        "+
                    " 'RAWDATA241' AS RAWDATA241, 'RAWDATA242' AS RAWDATA242, 'RAWDATA243' AS RAWDATA243, 'RAWDATA244' AS RAWDATA244,                        "+
                    " 'RAWDATA245' AS RAWDATA245, 'RAWDATA246' AS RAWDATA246, 'RAWDATA247' AS RAWDATA247, 'RAWDATA248' AS RAWDATA248,                        "+
                    " 'RAWDATA249' AS RAWDATA249, 'RAWDATA250' AS RAWDATA250, 'RAWDATA251' AS RAWDATA251, 'RAWDATA252' AS RAWDATA252,                        "+
                    " 'RAWDATA253' AS RAWDATA253, 'RAWDATA254' AS RAWDATA254, 'RAWDATA255' AS RAWDATA255, 'RAWDATA256' AS RAWDATA256,                        "+
                    " 'RAWDATA257' AS RAWDATA257, 'RAWDATA258' AS RAWDATA258, 'RAWDATA259' AS RAWDATA259, 'RAWDATA260' AS RAWDATA260,                        "+
                    " 'RAWDATA261' AS RAWDATA261, 'RAWDATA262' AS RAWDATA262, 'RAWDATA263' AS RAWDATA263, 'RAWDATA264' AS RAWDATA264,                        "+
                    " 'RAWDATA265' AS RAWDATA265, 'RAWDATA266' AS RAWDATA266, 'RAWDATA267' AS RAWDATA267, 'RAWDATA268' AS RAWDATA268,                        "+
                    " 'RAWDATA269' AS RAWDATA269, 'RAWDATA270' AS RAWDATA270, 'RAWDATA271' AS RAWDATA271, 'RAWDATA272' AS RAWDATA272,                        "+
                    " 'RAWDATA273' AS RAWDATA273, 'RAWDATA274' AS RAWDATA274, 'RAWDATA275' AS RAWDATA275, 'RAWDATA276' AS RAWDATA276,                        "+
                    " 'RAWDATA277' AS RAWDATA277, 'RAWDATA278' AS RAWDATA278, 'RAWDATA279' AS RAWDATA279, 'RAWDATA280' AS RAWDATA280,                        "+
                    " 'RAWDATA281' AS RAWDATA281, 'RAWDATA282' AS RAWDATA282, 'RAWDATA283' AS RAWDATA283, 'RAWDATA284' AS RAWDATA284,                        "+
                    " 'RAWDATA285' AS RAWDATA285, 'RAWDATA286' AS RAWDATA286, 'RAWDATA287' AS RAWDATA287, 'RAWDATA288' AS RAWDATA288,                        "+
                    " 'RAWDATA289' AS RAWDATA289, 'RAWDATA290' AS RAWDATA290, 'RAWDATA291' AS RAWDATA291, 'RAWDATA292' AS RAWDATA292,                        "+
                    " 'RAWDATA293' AS RAWDATA293, 'RAWDATA294' AS RAWDATA294, 'RAWDATA295' AS RAWDATA295, 'RAWDATA296' AS RAWDATA296,                        "+
                    " 'RAWDATA297' AS RAWDATA297, 'RAWDATA298' AS RAWDATA298, 'RAWDATA299' AS RAWDATA299, 'RAWDATA300' AS RAWDATA300,                        "+
                    " 'RAWDATA301' AS RAWDATA301, 'RAWDATA302' AS RAWDATA302, 'RAWDATA303' AS RAWDATA303, 'RAWDATA304' AS RAWDATA304,                        "+
                    " 'RAWDATA305' AS RAWDATA305, 'RAWDATA306' AS RAWDATA306, 'RAWDATA307' AS RAWDATA307, 'RAWDATA308' AS RAWDATA308,                        "+
                    " 'RAWDATA309' AS RAWDATA309, 'RAWDATA310' AS RAWDATA310, 'RAWDATA311' AS RAWDATA311, 'RAWDATA312' AS RAWDATA312,                        "+
                    " 'RAWDATA313' AS RAWDATA313, 'RAWDATA314' AS RAWDATA314, 'RAWDATA315' AS RAWDATA315, 'RAWDATA316' AS RAWDATA316,                        "+
                    " 'RAWDATA317' AS RAWDATA317, 'RAWDATA318' AS RAWDATA318, 'RAWDATA319' AS RAWDATA319, 'RAWDATA320' AS RAWDATA320,                        "+
                    " 'RAWDATA321' AS RAWDATA321, 'RAWDATA322' AS RAWDATA322, 'RAWDATA323' AS RAWDATA323, 'RAWDATA324' AS RAWDATA324,                        "+
                    " 'RAWDATA325' AS RAWDATA325, 'RAWDATA326' AS RAWDATA326, 'RAWDATA327' AS RAWDATA327, 'RAWDATA328' AS RAWDATA328,                        "+
                    " 'RAWDATA329' AS RAWDATA329, 'RAWDATA330' AS RAWDATA330, 'RAWDATA331' AS RAWDATA331, 'RAWDATA332' AS RAWDATA332,                        "+
                    " 'RAWDATA333' AS RAWDATA333, 'RAWDATA334' AS RAWDATA334, 'RAWDATA335' AS RAWDATA335, 'RAWDATA336' AS RAWDATA336,                        "+
                    " 'RAWDATA337' AS RAWDATA337, 'RAWDATA338' AS RAWDATA338, 'RAWDATA339' AS RAWDATA339, 'RAWDATA340' AS RAWDATA340,                        "+
                    " 'RAWDATA341' AS RAWDATA341, 'RAWDATA342' AS RAWDATA342, 'RAWDATA343' AS RAWDATA343, 'RAWDATA344' AS RAWDATA344,                        "+
                    " 'RAWDATA345' AS RAWDATA345, 'RAWDATA346' AS RAWDATA346, 'RAWDATA347' AS RAWDATA347, 'RAWDATA348' AS RAWDATA348,                        "+
                    " 'RAWDATA349' AS RAWDATA349, 'RAWDATA350' AS RAWDATA350, 'RAWDATA351' AS RAWDATA351, 'RAWDATA352' AS RAWDATA352,                        "+
                    " 'RAWDATA353' AS RAWDATA353, 'RAWDATA354' AS RAWDATA354, 'RAWDATA355' AS RAWDATA355, 'RAWDATA356' AS RAWDATA356,                        "+
                    " 'RAWDATA357' AS RAWDATA357, 'RAWDATA358' AS RAWDATA358, 'RAWDATA359' AS RAWDATA359, 'RAWDATA360' AS RAWDATA360,                        "+
                    " 'RAWDATA361' AS RAWDATA361, 'RAWDATA362' AS RAWDATA362, 'RAWDATA363' AS RAWDATA363, 'RAWDATA364' AS RAWDATA364,                        "+
                    " 'RAWDATA365' AS RAWDATA365, 'RAWDATA366' AS RAWDATA366, 'RAWDATA367' AS RAWDATA367, 'RAWDATA368' AS RAWDATA368,                        "+
                    " 'RAWDATA369' AS RAWDATA369, 'RAWDATA370' AS RAWDATA370, 'RAWDATA371' AS RAWDATA371, 'RAWDATA372' AS RAWDATA372,                        "+
                    " 'RAWDATA373' AS RAWDATA373, 'RAWDATA374' AS RAWDATA374, 'RAWDATA375' AS RAWDATA375, 'RAWDATA376' AS RAWDATA376,                        "+
                    " 'RAWDATA377' AS RAWDATA377, 'RAWDATA378' AS RAWDATA378, 'RAWDATA379' AS RAWDATA379, 'RAWDATA380' AS RAWDATA380,                        "+
                    " 'RAWDATA381' AS RAWDATA381, 'RAWDATA382' AS RAWDATA382, 'RAWDATA383' AS RAWDATA383, 'RAWDATA384' AS RAWDATA384,                        "+
                    " 'RAWDATA385' AS RAWDATA385, 'RAWDATA386' AS RAWDATA386, 'RAWDATA387' AS RAWDATA387, 'RAWDATA388' AS RAWDATA388,                        "+
                    " 'RAWDATA389' AS RAWDATA389, 'RAWDATA390' AS RAWDATA390, 'RAWDATA391' AS RAWDATA391, 'RAWDATA392' AS RAWDATA392,                        "+
                    " 'RAWDATA393' AS RAWDATA393, 'RAWDATA394' AS RAWDATA394, 'RAWDATA395' AS RAWDATA395, 'RAWDATA396' AS RAWDATA396,                        "+
                    " 'RAWDATA397' AS RAWDATA397, 'RAWDATA398' AS RAWDATA398, 'RAWDATA399' AS RAWDATA399, 'RAWDATA400' AS RAWDATA400,                        "+
                    " 'RAWDATA401' AS RAWDATA401, 'RAWDATA402' AS RAWDATA402, 'RAWDATA403' AS RAWDATA403, 'RAWDATA404' AS RAWDATA404,                        "+
                    " 'RAWDATA405' AS RAWDATA405, 'RAWDATA406' AS RAWDATA406, 'RAWDATA407' AS RAWDATA407, 'RAWDATA408' AS RAWDATA408,                        "+
                    " 'RAWDATA409' AS RAWDATA409, 'RAWDATA410' AS RAWDATA410, 'RAWDATA411' AS RAWDATA411, 'RAWDATA412' AS RAWDATA412,                        "+
                    " 'RAWDATA413' AS RAWDATA413, 'RAWDATA414' AS RAWDATA414, 'RAWDATA415' AS RAWDATA415, 'RAWDATA416' AS RAWDATA416,                        "+
                    " 'RAWDATA417' AS RAWDATA417, 'RAWDATA418' AS RAWDATA418, 'RAWDATA419' AS RAWDATA419, 'RAWDATA420' AS RAWDATA420,                        "+
                    " 'RAWDATA421' AS RAWDATA421, 'RAWDATA422' AS RAWDATA422, 'RAWDATA423' AS RAWDATA423, 'RAWDATA424' AS RAWDATA424,                        "+
                    " 'RAWDATA425' AS RAWDATA425, 'RAWDATA426' AS RAWDATA426, 'RAWDATA427' AS RAWDATA427, 'RAWDATA428' AS RAWDATA428,                        "+
                    " 'RAWDATA429' AS RAWDATA429, 'RAWDATA430' AS RAWDATA430, 'RAWDATA431' AS RAWDATA431, 'RAWDATA432' AS RAWDATA432,                        "+
                    " 'RAWDATA433' AS RAWDATA433, 'RAWDATA434' AS RAWDATA434, 'RAWDATA435' AS RAWDATA435, 'RAWDATA436' AS RAWDATA436,                        "+
                    " 'RAWDATA437' AS RAWDATA437, 'RAWDATA438' AS RAWDATA438, 'RAWDATA439' AS RAWDATA439, 'RAWDATA440' AS RAWDATA440,                        "+
                    " 'RAWDATA441' AS RAWDATA441, 'RAWDATA442' AS RAWDATA442, 'RAWDATA443' AS RAWDATA443, 'RAWDATA444' AS RAWDATA444,                        "+
                    " 'RAWDATA445' AS RAWDATA445, 'RAWDATA446' AS RAWDATA446, 'RAWDATA447' AS RAWDATA447, 'RAWDATA448' AS RAWDATA448,                        "+
                    " 'RAWDATA449' AS RAWDATA449, 'RAWDATA450' AS RAWDATA450, 'RAWDATA451' AS RAWDATA451, 'RAWDATA452' AS RAWDATA452,                        "+
                    " 'RAWDATA453' AS RAWDATA453, 'RAWDATA454' AS RAWDATA454, 'RAWDATA455' AS RAWDATA455, 'RAWDATA456' AS RAWDATA456,                        "+
                    " 'RAWDATA457' AS RAWDATA457, 'RAWDATA458' AS RAWDATA458, 'RAWDATA459' AS RAWDATA459, 'RAWDATA460' AS RAWDATA460,                        "+
                    " 'RAWDATA461' AS RAWDATA461, 'RAWDATA462' AS RAWDATA462, 'RAWDATA463' AS RAWDATA463, 'RAWDATA464' AS RAWDATA464,                        "+
                    " 'RAWDATA465' AS RAWDATA465, 'RAWDATA466' AS RAWDATA466, 'RAWDATA467' AS RAWDATA467, 'RAWDATA468' AS RAWDATA468,                        "+
                    " 'RAWDATA469' AS RAWDATA469, 'RAWDATA470' AS RAWDATA470, 'RAWDATA471' AS RAWDATA471, 'RAWDATA472' AS RAWDATA472,                        "+
                    " 'RAWDATA473' AS RAWDATA473, 'RAWDATA474' AS RAWDATA474, 'RAWDATA475' AS RAWDATA475, 'RAWDATA476' AS RAWDATA476,                        "+
                    " 'RAWDATA477' AS RAWDATA477, 'RAWDATA478' AS RAWDATA478, 'RAWDATA479' AS RAWDATA479, 'RAWDATA480' AS RAWDATA480,                        "+
                    " 'RAWDATA481' AS RAWDATA481, 'RAWDATA482' AS RAWDATA482, 'RAWDATA483' AS RAWDATA483, 'RAWDATA484' AS RAWDATA484,                        "+
                    " 'RAWDATA485' AS RAWDATA485, 'RAWDATA486' AS RAWDATA486, 'RAWDATA487' AS RAWDATA487, 'RAWDATA488' AS RAWDATA488,                        "+
                    " 'RAWDATA489' AS RAWDATA489, 'RAWDATA490' AS RAWDATA490, 'RAWDATA491' AS RAWDATA491, 'RAWDATA492' AS RAWDATA492,                        "+
                    " 'RAWDATA493' AS RAWDATA493, 'RAWDATA494' AS RAWDATA494, 'RAWDATA495' AS RAWDATA495, 'RAWDATA496' AS RAWDATA496,                        "+
                    " 'RAWDATA497' AS RAWDATA497, 'RAWDATA498' AS RAWDATA498, 'RAWDATA499' AS RAWDATA499, 'RAWDATA500' AS RAWDATA500,                        "+
                    " 'RAWDATA501' AS RAWDATA501, 'RAWDATA502' AS RAWDATA502, 'RAWDATA503' AS RAWDATA503, 'RAWDATA504' AS RAWDATA504,                        "+
                    " 'RAWDATA505' AS RAWDATA505, 'RAWDATA506' AS RAWDATA506, 'RAWDATA507' AS RAWDATA507, 'RAWDATA508' AS RAWDATA508,                        "+
                    " 'RAWDATA509' AS RAWDATA509, 'RAWDATA510' AS RAWDATA510, 'RAWDATA511' AS RAWDATA511, 'RAWDATA512' AS RAWDATA512,                        "+
                    " 'RAWDATA513' AS RAWDATA513, 'RAWDATA514' AS RAWDATA514, 'RAWDATA515' AS RAWDATA515, 'RAWDATA516' AS RAWDATA516,                        "+
                    " 'RAWDATA517' AS RAWDATA517, 'RAWDATA518' AS RAWDATA518, 'RAWDATA519' AS RAWDATA519, 'RAWDATA520' AS RAWDATA520,                        "+
                    " 'RAWDATA521' AS RAWDATA521, 'RAWDATA522' AS RAWDATA522, 'RAWDATA523' AS RAWDATA523, 'RAWDATA524' AS RAWDATA524,                        "+
                    " 'RAWDATA525' AS RAWDATA525, 'RAWDATA526' AS RAWDATA526, 'RAWDATA527' AS RAWDATA527, 'RAWDATA528' AS RAWDATA528,                        "+
                    " 'RAWDATA529' AS RAWDATA529, 'RAWDATA530' AS RAWDATA530, 'RAWDATA531' AS RAWDATA531, 'RAWDATA532' AS RAWDATA532,                        "+
                    " 'RAWDATA533' AS RAWDATA533, 'RAWDATA534' AS RAWDATA534, 'RAWDATA535' AS RAWDATA535, 'RAWDATA536' AS RAWDATA536,                        "+
                    " 'RAWDATA537' AS RAWDATA537, 'RAWDATA538' AS RAWDATA538, 'RAWDATA539' AS RAWDATA539, 'RAWDATA540' AS RAWDATA540,                        "+
                    " 'RAWDATA541' AS RAWDATA541, 'RAWDATA542' AS RAWDATA542, 'RAWDATA543' AS RAWDATA543, 'RAWDATA544' AS RAWDATA544,                        "+
                    " 'RAWDATA545' AS RAWDATA545, 'RAWDATA546' AS RAWDATA546, 'RAWDATA547' AS RAWDATA547, 'RAWDATA548' AS RAWDATA548,                        "+
                    " 'RAWDATA549' AS RAWDATA549, 'RAWDATA550' AS RAWDATA550, 'RAWDATA551' AS RAWDATA551, 'RAWDATA552' AS RAWDATA552,                        "+
                    " 'RAWDATA553' AS RAWDATA553, 'RAWDATA554' AS RAWDATA554, 'RAWDATA555' AS RAWDATA555, 'RAWDATA556' AS RAWDATA556,                        "+
                    " 'RAWDATA557' AS RAWDATA557, 'RAWDATA558' AS RAWDATA558, 'RAWDATA559' AS RAWDATA559, 'RAWDATA560' AS RAWDATA560,                        "+
                    " 'RAWDATA561' AS RAWDATA561, 'RAWDATA562' AS RAWDATA562, 'RAWDATA563' AS RAWDATA563, 'RAWDATA564' AS RAWDATA564,                        "+
                    " 'RAWDATA565' AS RAWDATA565, 'RAWDATA566' AS RAWDATA566, 'RAWDATA567' AS RAWDATA567, 'RAWDATA568' AS RAWDATA568,                        "+
                    " 'RAWDATA569' AS RAWDATA569, 'RAWDATA570' AS RAWDATA570, 'RAWDATA571' AS RAWDATA571, 'RAWDATA572' AS RAWDATA572,                        "+
                    " 'RAWDATA573' AS RAWDATA573, 'RAWDATA574' AS RAWDATA574, 'RAWDATA575' AS RAWDATA575, 'RAWDATA576' AS RAWDATA576,                        "+
                    " 'RAWDATA577' AS RAWDATA577, 'RAWDATA578' AS RAWDATA578, 'RAWDATA579' AS RAWDATA579, 'RAWDATA580' AS RAWDATA580,                        "+
                    " 'RAWDATA581' AS RAWDATA581, 'RAWDATA582' AS RAWDATA582, 'RAWDATA583' AS RAWDATA583, 'RAWDATA584' AS RAWDATA584,                        "+
                    " 'RAWDATA585' AS RAWDATA585, 'RAWDATA586' AS RAWDATA586, 'RAWDATA587' AS RAWDATA587, 'RAWDATA588' AS RAWDATA588,                        "+
                    " 'RAWDATA589' AS RAWDATA589, 'RAWDATA590' AS RAWDATA590, 'RAWDATA591' AS RAWDATA591, 'RAWDATA592' AS RAWDATA592,                        "+
                    " 'RAWDATA593' AS RAWDATA593, 'RAWDATA594' AS RAWDATA594, 'RAWDATA595' AS RAWDATA595, 'RAWDATA596' AS RAWDATA596,                        "+
                    " 'RAWDATA597' AS RAWDATA597, 'RAWDATA598' AS RAWDATA598, 'RAWDATA599' AS RAWDATA599, 'RAWDATA600' AS RAWDATA600 )                       "+
                    " )    ORDER BY CHARTID,CONTROLDATASEQ                                                                                                        ";

//            long startTime=System.currentTimeMillis();
            list=ora_spc.query(sql,new BeanListHandler<ChartData>(ChartData.class));
//            long endTime=System.currentTimeMillis();
//            logger.info("获取 chartData success count :{}----time:{} ms",list.size(),(endTime-startTime));
            return list;
        }catch (Exception e){
            logger.error("获取 chartData error :{}----sql:{}",e.getMessage(),str.toString());
            throw new Exception("获取 chartData Exception:"+ e.getMessage());
        }


    }
    public List<ChartSpec> getChartSpecByChartIdX(EtlConfD etlConfD, String chartId, String specType)throws Exception{
        //      logger.info("执行getChartSpecByChartId chartId:{},specType:{}",chartId,specType);
        List<ChartSpec> list =new ArrayList<>();
        String sql ="";
        StringBuffer str =new StringBuffer();
//        String panelInCondition = set2InCondition(charIdList,"CD.CHARTID");
        try{
            sql+=" SELECT ROW_NUMBER() OVER(ORDER BY CD.CONTROLDATASEQ) AS SEQ,CD.CHARTID, CD.CONTROLDATASEQ, DD.MATERIALNAME, DD.DCDATAID,                          "+
                    " TO_CHAR(DD.PROCESSTIME, 'YYYY-MM-DD HH24:MI:SS') AS PROCESSTIME, CASE WHEN MD.MARKINGTIME IS NULL THEN 'N' ELSE                        "+
                    " 'Y' END AS MARKINGFLAG, CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END AS RESULT, LISTAGG(ADS.ALARMSEQ, ',')                "+
                    " WITHIN GROUP (ORDER BY ADS.ALARMSEQ) AS OOSALARMID, LISTAGG(ADC.ALARMSEQ, ',') WITHIN GROUP (ORDER BY ADC.ALARMSEQ)                    "+
                    " AS OOCALARMID, ADR.ALARMSEQ AS OORALARMID, CDS.TARGET, CDS.UPPERSPECLIMIT, CDS.LOWERSPECLIMIT, CDS.CENTERLINE,                         "+
                    " CDS.UPPERCONTROLLIMIT, CDS.LOWERCONTROLLIMIT, CASE WHEN MD.MARKINGTIME IS NOT NULL THEN CASE WHEN CDR.RESULT = 'NA'                    "+
                    " THEN NULL ELSE CDR.RESULT END WHEN MAX(ADS.ALARMSEQ) > 0 THEN CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END                "+
                    " WHEN MAX(ADC.ALARMSEQ) > 0 THEN CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END WHEN ADR.ALARMSEQ > 0 THEN                   "+
                    " CASE WHEN CDR.RESULT = 'NA' THEN NULL ELSE CDR.RESULT END ELSE NULL END AS ALARMPOINT,                           "+
                    " CASE WHEN MD.MARKINGTIME IS NOT NULL THEN 'MAKING' WHEN MAX(ADS.ALARMSEQ) > 0 THEN 'OOS' WHEN MAX(ADC.ALARMSEQ) > 0                    "+
                    " THEN 'OOC' WHEN ADR.ALARMSEQ > 0 THEN 'OOR' ELSE NULL END AS ALARMTYPE FROM (SELECT /*+ INDEX(CD CONTROLDATA_IDX1) */ ROWNUM AS DATASEQ, "+
                    " CD.CHARTID, CD.CONTROLDATASEQ, CD.DCDATAID FROM SPC_CONTROLDATA CD       where 1=1                                                                   ";

//            str.append(" and CD.CHARTID = '495A1_A1870_WERP01_CD1_LOSS'  ");
            str.append(" and CD.CHARTID = '"+chartId+"'  ");
            //           str.append(" and CD.TIMEKEY >= '201806042350' AND CD.TIMEKEY < '201806050001' ");
            str.append(" and  CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())
                    +"' AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' ");
            sql+=str.toString();
            sql+=" ) CD INNER JOIN SPC_DCDATA DD ON DD.DCDATAID = CD.DCDATAID INNER JOIN SPC_ENUMDEFVALUE EDV ON EDV.ENUMNAME = 'XbarS'      ";
            sql+=" AND EDV.ENUMVALUE = '"+specType+"' ";
            sql+=" LEFT JOIN SPC_CONTROLDATARESULT CDR ON CDR.CHARTID = CD.CHARTID AND CDR.CONTROLDATASEQ = CD.CONTROLDATASEQ          "+
                    " AND CDR.SITENAME = EDV.UDF1 LEFT JOIN SPC_MARKINGDATA MD ON MD.CHARTID = CD.CHARTID AND MD.CONTROLDATASEQ = CD.CONTROLDATASEQ              "+
                    " LEFT JOIN SPC_ALARMDATA ADS ON ADS.CHARTID = CD.CHARTID AND ADS.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADS.CHARTNAME = EDV.ENUMVALUE       "+
                    " AND ADS.ALARMTYPE = 'OOS' LEFT JOIN SPC_ALARMDATA ADC ON ADC.CHARTID = CD.CHARTID AND ADC.CONTROLDATASEQ = CD.CONTROLDATASEQ AND           "+
                    " ADC.CHARTNAME = EDV.ENUMVALUE AND 1 = CASE WHEN EDV.ENUMVALUE = 'Xbar' THEN CASE WHEN ADC.CHARTNAME IN ('Xbar', 'Raw')                 "+
                    " THEN 1 ELSE 0 END ELSE CASE WHEN EDV.ENUMVALUE = ADC.CHARTNAME THEN 1 ELSE 0 END END AND ADC.ALARMTYPE = 'OOC'                         "+
                    " LEFT JOIN SPC_ALARMDATA ADR ON ADR.CHARTID = CD.CHARTID AND ADR.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADR.CHARTNAME = EDV.ENUMVALUE       "+
                    " AND 1 = CASE WHEN EDV.ENUMVALUE = 'Xbar' THEN CASE WHEN ADR.CHARTNAME IN ('Xbar', 'Raw') THEN 1 ELSE 0 END ELSE                        "+
                    " CASE WHEN EDV.ENUMVALUE = ADR.CHARTNAME THEN 1 ELSE 0 END END AND ADR.ALARMTYPE = 'OOR' LEFT JOIN SPC_CONTROLDATASPEC CDS ON               "+
                    " CDS.CHARTID = CD.CHARTID AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ AND CDS.CHARTNAME = EDV.ENUMVALUE WHERE 1 = 1                      "+
                    " GROUP BY CD.CHARTID,CD.CONTROLDATASEQ, DD.MATERIALNAME, DD.DCDATAID, DD.PROCESSTIME, MD.MARKINGTIME , CDS.TARGET, CDS.UPPERSPECLIMIT,             "+
                    " CDS.LOWERSPECLIMIT, CDS.CENTERLINE, CDS.UPPERCONTROLLIMIT, CDS.LOWERCONTROLLIMIT , CDR.RESULT, ADR.ALARMSEQ                            "+
                    " ORDER BY CD.CHARTID,SEQ  ";
//            long startTime=System.currentTimeMillis();
            list=ora_spc.query(sql,new BeanListHandler<ChartSpec>(ChartSpec.class));
//            long endTime=System.currentTimeMillis();
            //           logger.info("获取 ChartSpec success specType:{}----count :{}----time:{} ms",specType,list.size(),(endTime-startTime));
            return list;
        }catch (Exception e){
            logger.error("获取 ChartSpec error :specType:{}----{}----sql:{}",specType,e.getMessage(),str.toString());
            throw new Exception("获取 ChartSpec Exception:"+ e.getMessage());
        }

    }

}
